Multiple Choice Questions - Stored Procedures - Set 2
1. A return command . . . . . . . . . terminates the procedure and returns a value to the calling batch or client.
A) conditionally
B) unconditionally
2. A return value of . . . . . indicates success and is the default.
A) 0
B) 1
C) -1
D) None of above
3. With every returned record set, SQL Server will, by default, also send a message stating the number of rows affected or returned. To turn it off use:
A) SET NoCount OFF at the beginning of every stored procedure.
B) SET RowCount OFF at the end of every stored procedure.
C) SET NoCount ON at the beginning of every stored procedure.
D) SET RowCount ON at the end of every stored procedure.
4. If SQL Server doesn’t recognise the first string in a batch, it simply assumes that it is a name of a stored procedure.
A) True
B) False
5. The current user executing the procedure must also have the necessary permissions granted on the referenced objects in order to successfully execute the procedure. The only exception to this occurs when the objects referenced by a stored procedure . . . . . . . . . .
A) are sys objects
B) are also owned by the same user who owns the stored procedure
C) are only temp tables
B) both A & B
6. Which of the below statement is correct:
1.
DROP PROCEDURE dbo.My_Proc;
2.
DROP PROCEDURE dbo.My_Proc_1, dbo.My_Proc_2, dbo.My_Proc_3;
3.
DROP PROCEDURE IF EXISTS dbo.My_Proc_1;
A) Only 1 is correct
B) Both 1 & 2 are correct
C) All three are correct
D) None of above is correct
7. To save the value of the parameter in a variable that can be used in the calling program, the calling program must use the . . . . . . . keyword when executing the procedure.
A) INPUT
B) OPTIONAL
C) RETURN
D) OUTPUT
8. In SQL Server 2014, the objects referenced within a stored procedure do not have to exist at the time the procedure is created. One exception is when a stored procedure references . . . . . . . . . .
A) a table that does not exist
B) a variable that does not exist
C) another stored procedure that doesn’t exist
D) All of above
9. To display a list of existing procedures, query the . . . . . . . . . catalog view. To display the procedure definition, query the . . . . . . . . catalog view.
A) sys.catalog_view_name, sys.sql_modules
B) sys.objects, sys.database_usage
C) sys.database_usage, sys.catalog_view_name
D) sys.objects, sys.sql_modules
10. ALTER PROCEDURE changes permissions and also affect any dependent stored procedures or triggers.
A) True
B) False
Answers