Error Handling in SQL Server 2005

T-SQL's ability to handle errors in SQL Server 2000 and earlier editions is limited and cumbersome. Error-handling code isn't structured and many errors aren't trappable. SQL Server 2005 introduces new T-SQL error-handling capabilities that let you handle errors elegantly and efficiently. Let's review the main problems you face today when you need to write error-handling code, then I'll introduce SQL Server 2005's new error-handling construct and describe how it addresses the problems you face in earlier versions.

Error Handling Before SQL Server 2005

When you write error-handling code in T-SQL now, you face three main problems. First, there's no structured construct for identifying errors and handling them. All you have is the @@error() function, which returns an integer representing the way the previous statement finished. A zero means the statement was successful; any other value means an error occurred. You have to copy the value that @@error() returns for the statement into your own variable immediately after the statement that results in errors. Issuing another statement after the suspect one will cause the previous error ID that @@error() returns to be overridden and lost. So you usually end up with error-handling code after every suspect statement or with a nonstructured GOTO command that redirects your code to a label that marks the error-handling section that makes your code cumbersome.

Your second, and more significant, problem is that not all errors are trappable, which means that for some errors your error-handling code doesn't run. For example, most constraint violations are trappable, but conversion errors, deadlocks, and other errors terminate your batch. If the batch is terminated, then the errors aren't trappable. If a batch is terminated, the error-handling code doesn't run, so you have to handle errors in the client application instead of using T-SQL at the server level. Here's an example to illustrate what I mean. Run Listing 1 in SQL Server 2000 to create tables T1 and T2 in tempdb and populate each with one row that has a 1 value in col1, then run the following code to demonstrate errors that you can trap with T-SQL.

DECLARE @err AS int;INSERT INTO T1 VALUES(1);SET @err = @@error;PRINT 'After INSERT.Error number: ' + CAST(@err AS varchar(10)) + '.';

The code inserts a row that should cause a primary key violation error. You get an output that says, After INSERT. Error number: 2627, which means that the batch continued to run after SQL Server generates the error. Similarly, if you try to run the same code, substituting a 0 for the 1, you get a check constraint violation and the batch won't terminate. However, if you run the same code with the value a instead of 1, you get a conversion error, the batch terminates, and SQL Server doesn't invoke the PRINT statement at all. The PRINT statement here is for debug purposes. Typically, you have error-handling code instead of the debug PRINT statement, but when SQL Server generates a conversion error-or any other error that terminates your batch-your error handling code doesn't run.A third example of an error that you typically want to handle with T-SQL is a deadlock error. Some deadlocks are the result of poor programming or a lack of indexes, but others are intentional. You might design a deadlock to prevent consistency problems, such as lost updates. You'll usually want to handle intentional deadlocks by retrying the transaction. However, because a deadlock is an error that terminates the batch, the retry code never has a chance to run. To demonstrate that a deadlock terminates your batch, run the following code in one connection (call it Connection 1):

BEGIN TRANUPDATE T1 SET col1 = col1 + 1;

Then run the following code in another connection (call it Connection 2):

BEGIN TRANUPDATE T2 SET col1 = col1 + 1;

Now, go to Connection 1 and run the following query:

DECLARE @err AS int;SELECT * FROM T2;SET @err = @@error;PRINT 'After SELECT.Error number: ' + CAST(@err AS varchar(10)) + '.';COMMIT TRAN

Then go to connection 2 and run this query:

DECLARE @err AS int;SELECT * FROM T1;SET @err = @@error;PRINT 'After SELECT.Error number: ' + CAST(@err AS varchar(10)) + '.';COMMIT TRAN

SQL Server chooses one of the transactions (connections) as the deadlock victim and terminates it. When I run this code, SQL Server terminates the second transaction and generates the following output:

Msg 1205, Level 13, State 51, Line 2Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.Msg 0, Level 11, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.

Notice that SQL Server didn't invoke the PRINT statement, but instead terminated the batch. If you have retry logic in your code, it never runs. Now let's see how SQL Server 2005 solves these three problems.

Error Handling in SQL Server 2005

SQL Server 2005 introduces a new TRY/CATCH T-SQL construct for error handling. You write the code that might generate errors within a BEGIN TRY/END TRY block and write the error-handling code in a BEGIN CATCH/END CATCH block that immediately follows the TRY block. When SQL Server generates an error within a TRY block, SQL Server passes control to the corresponding CATCH block. If SQL Server generates an unhandled error (not within a TRY block), SQL Server passes control to the CATCH block associated with the nearest TRY block up the calling stack of routines (e.g., in a calling stored procedure or trigger). If no TRY block is found up the calling stack, SQL Server sends the error to the client application.

SQL Server 2005 lets you trap most errors that terminate a batch in SQL Server 2000, in addition to errors that terminate the connection (typically errors with severity-level 20 or higher, such as hardware errors).

Within the CATCH block, you can invoke the following functions to get information about the error SQL Server generates: ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), and ERROR_STATE(). The self-explanatory functions give you the four pieces of information related to an error: number, message text, severity, and state. Like @@error(), ERROR_NUMBER() gives you the error number. However, unlike @@error(), the value ERROR_NUMBER() returns-as well as the values the other three functions return-remains the same throughout the CATCH block. You can invoke all four functions anywhere in the CATCH block without being concerned that their values will change. You can still use the @@error() function for backward compatibility, but because of its volatile nature, you should use ERROR_NUMBER() instead.

Another function that you can invoke within the CATCH block is called XACT_STATE(), which returns the state of the transaction as an integer value: 0 means no transaction is active, 1 means a transaction is open and can be either committed or rolled back, and 2 means a transaction is open but can't be committed (failed state). Open locks aren't released because the transaction is still active, even though the transaction can't be committed (as opposed to no open transaction where all locks are released. You can only read data and must roll the transaction back before you can modify data. You can cause all transactions to enter a failed state upon error by turning on the session option XACT_ABORT. Remember that we're using beta software, so the values that the functions return that represent the different transaction states might change in the final release.

Now, let's look at more code examples. First, run Listing 1 in SQL Server 2005 to create the T1 and T2 tables in tempdb and populate each with one row that a 1 value in col1. Then, run Listing 2, which issues an INSERT statement within a TRY block. Listing 2's INSERT statement completes successfully. The output shows No error, and the CATCH block is completely skipped. If you run Listing 2 again, you get the following output, which indicates a primary key violation because the primary key value 10 already exists in the table:

In CATCH block.Error number: 2627Error message: Violation of PRIMARY KEY constraint 'PK_T1'. Cannot insert duplicate key in object 'T1'.Error severity: 14Error state: 1XACT_STATE: 0

Because Listing 2 generates a primary key violation error, SQL Server passed control to the CATCH block, which printed all the debug information. In production code, instead of the debug print commands, you'd place your error-handling code where you inspect the error and determine a course of action. If you run Listing 2 again, replacing the value 10 with 0, you generate a check constraint violation. Then replace the value with a, which generates a conversion error. As you see, all errors are trappable in SQL Server 2005.

As a more detailed example, the code in Listing 3 handles a deadlock error. The code has retry logic that attempts to rerun the transaction three times if it generates deadlocks. The code keeps track of the attempt number in the @retry variable and loops when @retry is between 1 and 3. Upon a successful termination of the transaction, the code sets the @retry value to 0, preventing the loop from iterating again. When SQL Server detects a deadlock, the CATCH block prints some debug information, increments the @retry value by 1, and enters a delay of 3 seconds so the other process will have time to release the locks before the next attempt to issue the transaction. After more than three retries, the CATCH block prints a message indicating three failed attempts. Typically, you'd raise an error here that returns to the calling routine (or the client application). Now run Listing 3's code first with no other conflicting activity submitted from other sessions. The output shows the contents of T2 (which the SELECT statement returns) and a print message that says Transaction finished successfully. Because SQL Server didn't generate an error, SQL Server skips the catch block. To create the deadlock, copy the code from Listing 3 to a second connection and swap the table names in the UPDATE and SELECT statements of the second connection (UPDATE T2 and SELECT * FROM T1). Execute the code in the first connection and before 10 seconds pass, run the code in the second connection. I introduced a delay of 10 seconds between the UPDATE and SELECT statements to accommodate the delay between the time you invoke the code in the first and the second connections. SQL Server generates a deadlock error because we introduced a nonresolvable conflict. SQL Server terminates the second transaction, releasing its locks, and lets the first transaction finish successfully. The second connection enters a delay of 3 seconds, then tries again to issue the transaction. Because the first transaction has finished and released the locks, the second connection's second attempt completes successfully. The output that the first connection generates includes T2's contents and a print message that says the transaction completed successfully. The second connection generates the output that Figure 1 shows. As you can see, our error-handling code traps and handles the deadlock error.

Robust T-SQL Error Handling

SQL Server 2005 tackles the main problems you encounter when writing error-handling code in previous versions. The TRY/CATCH construct lets you write structured, elegant error-handling code and trap errors that you couldn't before. You can investigate error information within the CATCH block by invoking new functions that show you the error ID, error message text, error severity, error state, and the transaction state. The values those functions return don't change during the CATCH block, so you don't need to copy them aside to a variable like you do when using the @@error() function. SQL Server 2005 gives you robust means to handle errors by using T-SQL, so you aren't forced to deal with errors in the client application where it's not appropriate.