Stored Procedures  «Prev  Next»

SQL Server Stored Procedures - Quiz

Each question is worth one point. Select the best answer or answers for each question.
 

1. What makes stored procedures faster than sending a Transact-SQL statement to SQL Server?
Please select the best answer.
  A. They are not faster.
  B. The query plan is compiled and cached on the server.
  C. The Transact-SQL statements are shorter.
  D. There are less commands available, so parsing time is shorter.

2. Alison wants to make sure that her query plan is guaranteed to be up to date when executing a stored procedure. She does not mind if it takes longer to execute that stored procedure. How can she ensure this?
Please select the best answer.
  A. Use the WITH RECOMPILE option when creating the stored procedure.
  B. Use the WITH UPDATE_QUERY_PLAN option when creating the stored procedure.
  C. Run the RECOMPILE Transact-SQL statement to force a recompile.
  D. Query plans are always up to date, so this is not necessary.

3. How can you create an extended stored procedure?
Please select the best answer.
  A. You cannot create an extended stored procedure because they come preinstalled with SQL Server 2012.
  B. With the CREATE EXTENDED PROCEDURE Transact-SQL statement.
  C. With the CREATED PROCEDURE EXT Transact-SQL statement.
  D. With Visual C++.
4. If you wish to return a value from a stored procedure into a parameter, which of the following calls would you use?
Please select the best answer.
  A. EXECUTE usp_TrimSpace 5
  B. EXECUTE usp_TrimSpace 5 OUTPUT
  C. EXECUTE usp_TrimSpace @val OUTPUT
  D. EXECUTE usp_TrimSpace @val

5. Consider the following Transact-SQL statement fragment from a stored procedure. What happens if an error occurs during the INSERT statement? 
INSERT INTO Temp (Col1)
VALUES @TempVal
PRINT @TempVal
IF @@error <> 0
  
RETURN 1
ELSE
  
RETURN 0
…MORE CODE BELOW…

Please select the best answer.
  A. The stored procedure continues to execute more code below.
  B. The stored procedure exits with a return value of 1.
  C. The stored procedure exits with a return value of 0.
  D. The stored procedure exits with no return value.
Correct answers:

Your Score: 0