Exception handling best practices in SQL Server 2005

SQL Server 2005 brings you TRY/CATCH, a new T-SQL feature that helps find and address errors before they reach application code. Get best practices for working with the new feature.

  Exception handling is a key component of any solid software development methodology. Fact is, bugs do exist and

exceptions will occur at some point when code runs. But what should you do to deal with exceptions and ensure they don't cause problems? Unfortunately, when programming SQL Server stored procedures, the answer has historically been, "Don't do anything. You can't!"

The lack of exception control features has made SQL Server programming a headache-inducing experience at times, and use of the term "exception handling" in the same sentence with T-SQL is a laughable misnomer.

Luckily for SQL Server developers, times (and feature sets) have changed. T-SQL has grown up in a big way when it comes to dealing with exceptions. SQL Server 2005 ships with the ability to wrap T-SQL code in special blocks that enable developers to not only determine when an error occurred, but actually catch it and do something about it as well. This means that it's finally possible to encapsulate data exceptions and keep them where they belong -- inside the database and out of the application code.

TABLE OF CONTENTS
   The old way: @@ERROR
   The new way: TRY/CATCH
   How to handle transactions
   When to handle exceptions

 
The old way: @@ERROR

SQL Server versions before 2005 offered only one simple way to work with exceptions: the @@ERROR function. This function can be used to determine if an error occurred in the last statement that was executed before evaluating @@ERROR. For example:

SELECT 1/0
SELECT @@ERROR

-----------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

-----------
8134

(1 row(s) affected)

In this case @@ERROR returns 8134, which is the error number for a divide-by-zero error.

Using @@ERROR, you can detect errors and control them to some degree. However, proper use of this function requires that you check it after every statement; otherwise it will reset, as shown in the following example:

 SELECT 1/0 IF @@ERROR <> 0 BEGIN SELECT @@ERROR END

-----------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

-----------
0

(1 row(s) affected)

Trying to catch the error in this case actually ends up resetting it; the @@ERROR in the SELECT returns 0 rather than 8134 because the IF statement did not throw an exception.

In addition to the fact that the exception resets after each statement, @@ERROR does not actually handle the exception -- it only reports it. The exception is still sent back to the caller, meaning that even if you do something to fix the exception in your T-SQL code, the application layer will still receive a report that it occurred. This can mean additional complexity when creating application code because you need to handle exceptions that may needlessly bubble up from stored procedures.
 
The new way: TRY/CATCH

In SQL Server 2005, exceptions can now be handled with a new T-SQL feature: TRY/CATCH blocks. This feature emulates the exception handling paradigm that exists in many languages derived from the C family, including C/C++, C#, Java and JavaScript. Code that may throw an exception is put into a try block. Should an exception occur anywhere in the code within the try block, code execution will immediately switch to the catch block, where the exception can be handled.

The term "catch" is of special importance here. When TRY/CATCH is used, the exception is not returned to the client. It is "caught" within the scope of the T-SQL that caused it to be thrown.

For an example of TRY/CATCH, consider a divide-by-zero error:

 BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH SELECT 'Error Caught' END CATCH

-----------

(0 row(s) affected)

------------
Error Caught

(1 row(s) affected)

When this batch is run, no exception is reported. Instead, the message "Error Caught" is selected back. Of course, your T-SQL code does not have to send back any kind of specific message in the CATCH block. Any valid T-SQL can be used, so you can log the exception or take action to remedy the situation programmatically, all without reporting it back to the caller.

While merely being able to catch an exception is a great enhancement, T-SQL is also enhanced with new informational functions that can be used within the CATCH block. These functions are: ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_LINE(), ERROR_SEVERITY(), ERROR_STATE() and ERROR_PROCEDURE(). Unlike @@ERROR, the values returned by these functions will not reset after each statement and, as a result, the functions will return consistent values over the entire time a CATCH block is executed. For instance:

 BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH SELECT 'Error Caught' SELECT
ERROR_MESSAGE(), ERROR_NUMBER() END CATCH

-----------

(0 row(s) affected)

------------
Error Caught

(1 row(s) affected)

-------------------------------------------- ---------------
Divide by zero error encountered. 8134

(1 row(s) affected)

In this example, the ERROR_MESSAGE() and ERROR_NUMBER() functions return the correct values, even though a SELECT occurred between the exception and evaluation of the functions -- quite an improvement over @@ERROR!
 
How to handle transactions

Error control is important in database programming because it gives you the ability to roll back transactions in response to problems. By default, SQL Server typically does not stop transactions due to exceptions, which can result in invalid data. Consider the following batch:

 CREATE TABLE Funds ( Amount INT CHECK (Amount > 0) ) BEGIN TRANSACTION
INSERT Funds VALUES (10) INSERT Funds VALUES (-1) COMMIT TRANSACTION
SELECT * FROM Funds

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 9
The INSERT statement conflicted with the CHECK constraint "CK__Funds__Amount__67A95F59". The conflict occurred in database "master", table "dbo.Funds", column 'Amount'.
The statement has been terminated.
Amount
-----------
10

(1 row(s) affected)

In this case, a table called Funds is created, which includes a CHECK constraint on the Amount column to ensure that amounts are greater than 0. Once the table is created, a transaction starts. This implies that INSERTs will be atomic -- all values or no values should be inserted. However, even though an exception occurs due to violation of the CHECK constraint, the transaction is committed and one of the values remains in the table.

Implementing TRY/CATCH in this situation solves the problem outright:

 BEGIN TRY BEGIN TRANSACTION INSERT Funds VALUES (10) INSERT Funds VALUES
(-1) COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK END CATCH

Now, any exception in the TRY block immediately causes code execution to shift to the CATCH block, thereby rolling back the transaction and ensuring that invalid values stay out of the table.
 
When to handle exceptions

Exception handling is new to SQL Server, so the question of when to handle exceptions may be new to many DBAs and database developers. Here I will supply a few general guidelines to help you get started.

1. Overuse is much better than underuse when it comes to dealing with exceptions. As illustrated in the transaction example, failure to properly handle exceptions when they occur leaves you with invalid data in the database. Imagine a database being used to back financial transactions and think of the possibilities. Exception handling is an absolute necessity when you care about the quality of your data.

2. Strive to use a TRY/CATCH block whenever you use an explicit transaction and whenever you modify data. Some practitioners advocate using TRY/CATCH blocks in every stored procedure in order to log any exception that occurs in the database. Although this seems like overkill for some applications, it can be a good model for applications that require extreme integrity. Again, consider financial transactions.

3. Even though you may often use TRY/CATCH to facilitate structured exception logging, try to remember that not getting exceptions at all is far more desirable than just catching them when they occur. Heavily test your code and the code around problems you know exist, rather than letting exception handlers deal with them for you. Just because the exception is caught does not mean that it didn't occur. Exception handling is no excuse for sloppy coding techniques. If anything, it should give you a chance to more readily discover where your problems lie and fix them.

Summary

Exception handling in SQL Server has come a long way, thanks to the introduction of TRY/CATCH in SQL Server 2005. It is now much easier to control exceptions and enforce data integrity within the database. DBAs and developers should start using this feature immediately upon migration to SQL Server 2005 in order to move toward better, more robust T-SQL code.

ABOUT THE AUTHOR
Adam Machanic is a database-focused software engineer, writer and speaker based in Boston, Mass. He has implemented SQL Server for a variety of high-availability OLTP and large-scale data warehouse applications and also specializes in .NET data access layer performance optimization. He is a Microsoft Most Valuable Professional (MVP) for SQL Server and a Microsoft Certified Professional. Machanic is co-author of Pro SQL Server 2005, published by Apress.

More information from SearchSQLServer.com

This was first published in May 2006

Dig deeper on Microsoft SQL Server 2005

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close