TRY...CATCH in SQL Server 2005
By Scott Mitchell
An Easier Approach to Rolling Back Transactions in the Face of an Error
SQL Server 2005 offers a number of new features over its predecessor, including many features aimed at making working with databases more like writing .NET application code. For example, in SQL Server 2005, stored procedures, triggers, UDFs, and so on can be written using any .NET Framework programming language (such as Visual Basic or C#). Another feature, and the focus of this article, is SQL Server 2005's support for TRY...CATCH blocks. TRY...CATCH blocks are the standard approach to exception handling in modern programming languages, and involve:
- A TRY Block - the TRY block contains the instructions that might cause an exception
- A CATCH Block - if an exception occurs from one of the statements in the TRY block, control is branched to the CATCH block, where the exception can be handled, logged, and so on.
Prior to SQL Server 2005, detecting errors resulting from T-SQL statements could only be handled by checking a global error
@@ERROR. Because the
@@ERROR variable value is reset after each SQL statement, this antiquated approach leads to rather bloated
stored procedures, as the variable must be checked after each statement with code to handle any problems.
The TRY...CATCH block in SQL Server 2005 offers a much more readable syntax and one that developers are more familiar with. In this article we'll look at the new TRY...CATCH block and examine how it can be used to rollback a transaction in the face of an error. Read on to learn more!
@@ERROR - the Old Way of Handling Errors in T-SQL
SQL Server provides an
@@ERROR variable that indicates the status of the last completed SQL statement in
a given batch. If a SQL statement is completed successfully,
@@ERROR is assigned 0. If, however, an error
@@ERROR is set to the number of the error message.
To see how the
@@ERROR variable can be
used, imagine that we have a data-driven web application that maintains employee information. Let's assume that our database
EmployeePhoneNumbers tables, among others. These two tables share a one-to-many relationship;
that is, each
Employees record can have an arbitrary number of related records in the
table. There might be one for their office phone, one for their pager, one for their cell phone, and so on. Imagine that
our database includes a stored procedure,
DeleteEmployee, which is comprised of two
DELETE statements - one to delete the employee's
related phone numbers from the system and one to delete the actual employee record:
Since we want these two delete statements to be atomic and either both fail or both succeed, we need to wrap up these two statements into a transaction. By using a transaction, we can rollback the transaction in the face of an error and undo any changes made since the start of the exception. To accomplish this we might initially try to use the following syntax:
This stored procedure (it appears) starts a transaction, runs the two
DELETE statements, and then checks to
see if there was an error. If there was one, it rolls the transaction back, else it commits the transaction. I say "it appears"
because this syntax, while legal, is semantically incorrect because the
@@ERROR variable is set after every
SQL statement. Therefore, if the first
DELETE statement has an error the
@@ERROR variable will be
set to its error number. Then, the second
DELETE will execute. If this second
@@ERROR will be set back to 0, in which case the transaction will be committed even though there was a problem with
the first statement! Whoops!
Instead, a check must be made after every SQL statement to see if there has been an error. If so, the transaction must be
rolled back and the stored procedure exited. This can lead to bulky scripts as a stored procedure with, say, five statements will
have five checks against the
@@ERROR variable. And if you forget to cut and paste a check in for a particular
statement you're opening yourself up to a potential problem.
For more information on transactions and the
@@ERROR syntax used for checking for errors and rolling back
as needed, see Managing Transactions in SQL Server Stored Procedures.
Handling Errors With SQL Server 2005's TRY...CATCH Blocks
While SQL Server 2005 still supports the
@@ERROR approach, a better alternative exists with its new TRY...CATCH
blocks. As with programming languages like Visual Basic, C#, and Java, the SQL Server 2005 TRY...CATCH block executes a number
of statements in the TRY block. If there are no errors in any of the statements, control proceeds to after the CATCH block.
If, however, one of the statements causes an error, control branches immediately to the start of the CATCH block.
Furthermore, like programming languages, nested TRY...CATCH blocks are allowed, meaning that you can have an entire TRY...CATCH
block in the TRY or CATCH portions of an "outter" TRY...CATCH block.
The following system functions are available in the CATCH block and can be used to determine additional error information:
|Returns the number of the error|
|Returns the severity|
|Returns the error state number|
|Returns the name of the stored procedure or trigger where the error occurred|
|Returns the line number inside the routine that caused the error|
|Returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times|
Also realize that not all errors generating by the TRY block statements are passed onto the CATCH block. Any errors with a severity of 10 or less are considered to be warnings and do not branch control flow to the CATCH block. Also, any errors that sever the database connection will not cause the CATCH block to be reached.
Let's look at a quick example of using TRY...CATCH, after which we'll turn our attention to using this new construct for
rolling back transactions in the face of an error. The following example shows a very simply
on the Northwind database's
Products table. The
ProductID column is
IDENTITY column and therefore its value can't be specified when inserting a new record. However, I've specified
this value in the following
INSERT statement. Hence, control is turned over to the CATCH block where error information is
This query will return a single record with a single column with the contents: "There was an error! Cannot insert explicit value for identity column in table 'Products' when IDENTITY_INSERT is set to OFF."
Using TRY...CATCH to Rollback a Transaction in the Face of an Error
As discussed earlier in this article, one of the downsides of the
@@ERROR variable approach is that for
transactions a check against this variable must be added after each and every SQL statement to determine if an error occurred
and, if so, to rollback the transaction. With SQL Server 2005's TRY...CATCH block, however, these types of scripts are greatly
simplified, as the following example illustrates:
In the TRY block a transaction is started and the two
DELETE statements are performed. If both
COMMIT will be reached and the transaction committed. If, however, either one produces an error,
control will be routed to the CATCH block where the transaction will be rolled back. Also, the CATCH block re-raises the
RAISERROR) so that
the error information will be percolated up to the application that invoked the stored procedure. For an ASP.NET web
application, that means that an exception will be raised in the .NET code that invoked this stored procedure, as chances
are you not only want to rollback the transaction, but have some sort of error message handled in the web application as well
so that the end user knows that their action failed.
Adding the call to
RAISERROR in the CATCH block is tantamount to having a TRY...CATCH block in a programming
language that re-throws the exception in the CATCH block after logging it or performing other actions. The action performed
in the example above is rolling back the transaction, but could also include logging logic.
If you omit the
RAISERROR, the ASP.NET application won't throw an exception from executing the database command.
Chances are you want to have an exception thrown on the ASP.NET side (so that you don't fail silently). If so, leave in
SQL Server 2005's new TRY...CATCH block brings the familiar TRY...CATCH exception handling to T-SQL. Prior to SQL Server 2005, errors could only be detected in SQL scripts through the use of the
@@ERROR variable, which annoyingly reset after
each SQL statement, thereby requiring checks after each and every statement. This led to bloated script that was prone to
typos or cut and paste errors leading to potentially serious problems. With SQL Server 2005's TRY...CATCH block, on the other hand,
anytime an error is raised by one of the statements in the TRY block, control is directed to the CATCH block. As we saw in this
article, the TRY...CATCH block allows for much more readable and cleaner error handling in transaction settings.