Stored Procedures  «Prev  Next»

SQL Server Stored Procedures – Quiz

Each question is worth one point. Select the best answer for each question.
1. What is the primary reason stored procedures can perform better than ad-hoc Transact-SQL statements?
Select the best answer.
A. They reduce the number of Transact-SQL keywords.
B. The execution plan can be cached and reused by SQL Server.
C. They always bypass query optimization.
D. They eliminate the need for indexes.

2. A developer wants SQL Server to generate a fresh execution plan every time a stored procedure runs. Which option supports this behavior?
Select the best answer.
A. CREATE PROCEDURE WITH NO CACHE
B. CREATE PROCEDURE WITH RECOMPILE
C. SET FORCEPLAN ON
D. EXECUTE RECOMPILE

3. Which statement best describes extended stored procedures in modern SQL Server versions?
Select the best answer.
A. They are the recommended method for external logic integration.
B. They are deprecated and should be avoided in favor of CLR or external services.
C. They are required for replication.
D. They are created using CREATE EXTENDED PROCEDURE.

4. Which EXEC statement correctly captures a value returned through an OUTPUT parameter?
Select the best answer.
A. EXEC usp_TrimSpace 5
B. EXEC usp_TrimSpace 5 OUTPUT
C. EXEC usp_TrimSpace @val OUTPUT
D. EXEC usp_TrimSpace OUTPUT

5. In modern SQL Server, which construct is recommended for handling errors inside a stored procedure?
Select the best answer.
A. @@ERROR checks after each statement
B. IF @@ROWCOUNT = 0
C. TRY…CATCH with THROW
D. PRINT error messages only





SEMrush Software