Implementing Error Handling in Stored Procedures

• Implement error handling in stored procedures.

Stored procedures can also manage some error conditions, whether caused by data exceptions or by problems with SQL Server. There are ways to handle errors inside a stored procedure.

First of all, judicious use of return codes is recommended to ensure that the applications upstream understand the execution status of the stored procedure. Within SQL Server, using the integer return codes is a good way to make sure that calling stored procedures understand how called stored procedures completed their tasks . It is important to create written standards on how return codes should be used, such as whether the return value is a "success" value or a "failed" value. Some systems use as success, so that the other numbers are available to denote specific types of errors.

Next , make sure you are using transactions, @@TRANCOUNT , and ROLLBACK correctly within the stored procedure. That's all covered back in Chapter 6.

Some T-SQL statements are more likely to cause errors than others, such as statements that perform BULK INSERT operations or that interact with other servers using the Distributed Transaction Coordinator. To discover whether a particular T-SQL statement was successful, you can check the value of the @@ERROR global variable. This variable is set similar to how @@ROWCOUNT is set in that it is valid only if you check it immediately following the statement that caused the error. If the @@ERROR variable is set to zero, then no error occurred during execution of the preceding statement. If @@ERROR is set to something other than zero, then an error occurred during the attempt to run the statement. The @@ERROR variable can be used to handle errors that won't stop a batch (or a stored procedure) from running, such as constraint violations or problems with aggregate functions that don't have enough data.

Here's an example of what error handling in a stored procedure might look like:

 CREATE PROC errhandler AS BEGIN DECLARE @ErrorCollector int BEGIN TRANSACTION INSERT INTO mytable VALUES (1, 4, 3, 19)        SET @ErrorCollector = @@ERROR IF @ErrorCollector <> 0 begin ROLLBACK TRANSACTION RETURN @ERRORCOLLECTOR end else COMMIT TRANSACTION return 0 END 

A few notes on this. First of all, using BEGIN and END to wrap the whole thing up is not required; it's just another style you can use. The @ErrorCollector variable is used to hold the @@ERROR value for the return code, if needed. The function also uses transactions to handle data integrity, and returns a non-zero number if the stored procedure fails, which represents the error code.

Stored procedures have two options that can be set when they are being created or altered : ENCRYPTION and RECOMPILE . The ENCRYPTION option specifies that the text of the stored procedure should not be available in syscomments or to the sp_helptext stored procedure. This is typically done when you are distributing proprietary code to customers and you don't want them to be able to see your code. It also typically has to be removed before you install version upgrades and some service pack upgrades for SQL Server, which is a good reason to not use it, because removing the encryption means re-creating all the stored procedures.

The RECOMPILE option specifies that the stored procedure's execution plan should be recompiled before it is executed. This is used when the queries in the stored procedure change significantly based on incoming parameters ”changes that would change the indexes or join strategies SQL Server uses. To use either of these options, the syntax is:

 create procedure myproc @MyParameter int with recompile as        do stuff here go 

Whenever a user executes a stored procedure, SQL Server checks the stored procedure cache, which is a part of the memory that SQL Server uses for disk cache, to see whether the stored procedure execution plan is present in the cache. If it is, then SQL Server uses the cache to avoid having to figure out the execution plan again. The problem is that sometimes you want SQL Server to re-figure the execution plan. For example, if the stored procedure references tables that have indexes that change, or if the stored procedure just has an extremely convoluted plan, you may want to force a recompile by creating the stored procedure with the RECOMPILE option. If the stored procedure has already been created without the RECOMPILE option, you can change it to recompile on execution by using the sp_recompile system stored procedure. If you want to recompile the plan just once, you can use the WITH RECOMPILE option for the EXEC statement to execute the stored procedure ”just tack WITH RECOMPILE onto the end of the EXEC .

REVIEW BREAK

• Stored procedures are objects stored in the database that contain code similar to batches.

• Stored procedures can return data as a rowset, an output parameter, a cursor, or by populating a temporary table.

• Stored procedures can be used to encapsulate business processes by validating data and controlling transactions.

• Stored procedures can also be used to filter data sent to applications.

Stored procedures are vitally important for applications that use SQL Server, and you'll run into them constantly. The next section is going to cover very similar objectives, but the "user-defined function" part of the objective is structured similarly to what you've been doing on stored procedures. User-defined functions are very useful and have a lot of useful applications.