Error Handling and Streamlining Stored Procedures

Introduction

The Error Handling and Streamlining Stored Procedures module provides you with the instructions and devices to develop your hands on skills in the following topic.

• Using error handling and streamlining methods in stored procedures

Lab time: It will take approximately 40 minutes to complete this lab.

Exam Objectives

Four exam objectives are covered in this lab.

• Demonstrate how to handle the errors that occur in a stored procedure using TRY…CATCH block
• Demonstrate how to create a stored procedure using the WITH ENCRYPTION option to secure the text of the stored procedure
• Demonstrate how to create a stored procedure using the WITH RECOMPILE option
• Demonstrate how to create a stored procedure using the EXECUTE AS clause

Exercise 1 - Using Error Handling and Streamlining Methods in Stored Procedures

A stored procedure is a group of Transact-SQL statements that are compiled into a single execution plan. There are different methods to handle errors and streamline the stored procedures to produce optimum results. The various methods are as follows:

• TRY…CATCH block: You can use the TRY...CATCH error handling blocks in stored procedures. When an error is encountered in a TRY block placed within a stored procedure, the control passes to the first statement in the associated CATCH block. After executing all the statements in the associated CATCH block, the control passes to the calling statement of the stored procedure if END CATCH is the last statement in the stored procedure.
• WITH ENCRYPTION option: You can use the WITH ENCRYPTION option with stored procedures to encrypt the text in these procedures. Encryption of stored procedures is necessary as it contains the business logic of the applications. The business logic in the stored procedures cannot be shared with the end users for security reasons.
• WITH RECOMPILE option: You can use the WITH RECOMPILE option in stored procedures to get a new execution plan each time it is run. This option, when used with stored procedures, enhances the query performance. The different ways to use the WITH RECOMPILE option are as follows:
• WITH RECOMPILE option in the procedure definition or procedure call
• RECOMPILE query hint included in the Transact-SQL statements
• sp_recompile system stored procedure to recompile an existing procedure
• EXECUTE AS clause: You can use the EXECUTE AS clause to define the execution context. You specify the owner of the procedure to help manage permissions and accessibility of the procedure.

In this exercise, you will learn to handle errors and streamline stored procedures.