TRY...CATCH in SQL Server 2005 An Easier Approach to Rolling Back Transactions in the Face of an Error

• ชอบบทความนี้มากเลย แต่ post ก่อนหน้านี้ แปะไว้แค่ลิ้ง
• อันนี้เอาของเค้ามาแปะเลยแล้วกัน
• ความรู้ดีๆ จาก http://www.4guysfromrolla.com/webtech/041906-1.shtml
• By Scott Mitchell

Introduction
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.

See the Wikipedia Exception Handling entry for more information on the TRY...CATCH construct as well as exception handling concepts in general.

Prior to SQL Server 2005, detecting errors resulting from T-SQL statements could only be handled by checking a global error variable, @@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!

Checking @@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 occurs, @@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 has Employees and 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 EmployeePhoneNumbers 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:

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int ) AS -- Delete the Employee's phone numbers DELETE FROM EmployeePhoneNumbers WHERE EmployeeID = @EmployeeID -- Delete the Employee record DELETE FROM Employees WHERE EmployeeID = @EmployeeID

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:

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int ) AS BEGIN TRANSACTION -- Start the transaction -- Delete the Employee's phone numbers DELETE FROM EmployeePhoneNumbers WHERE EmployeeID = @EmployeeID -- Delete the Employee record DELETE FROM Employees WHERE EmployeeID = @EmployeeID -- See if there is an error IF @@ERROR <> 0 -- There's an error b/c @ERROR is not 0, rollback ROLLBACK ELSE COMMIT -- Success! Commit the transaction

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 DELETE succeeds, @@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.

BEGIN TRY Try Statement 1 Try Statement 2 ... Try Statement M END TRY BEGIN CATCH Catch Statement 1 Catch Statement 2 ... Catch Statement N END CATCH

The following system functions are available in the CATCH block and can be used to determine additional error information:

Function	Description
ERROR_NUMBER()	Returns the number of the error
ERROR_SEVERITY()	Returns the severity
ERROR_STATE()	Returns the error state number
ERROR_PROCEDURE()	Returns the name of the stored procedure or trigger where the error occurred
ERROR_LINE()	Returns the line number inside the routine that caused the error
ERROR_MESSAGE()	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 INSERT query on the Northwind database's Products table. The Products table's ProductID column is an 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 displayed.

BEGIN TRY -- This will generate an error, as ProductID is an IDENTITY column -- Ergo, we can't specify a value for this column... INSERT INTO Products(ProductID, ProductName) VALUES(1, 'Test') END TRY BEGIN CATCH SELECT 'There was an error! ' + ERROR_MESSAGE() END CATCH

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:

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int ) AS BEGIN TRY BEGIN TRANSACTION -- Start the transaction -- Delete the Employee's phone numbers DELETE FROM EmployeePhoneNumbers WHERE EmployeeID = @EmployeeID -- Delete the Employee record DELETE FROM Employees WHERE EmployeeID = @EmployeeID -- If we reach here, success! COMMIT END TRY BEGIN CATCH -- Whoops, there was an error IF @@TRANCOUNT > 0 ROLLBACK -- Raise an error with the details of the exception DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY() RAISERROR(@ErrMsg, @ErrSeverity, 1) END CATCH

In the TRY block a transaction is started and the two DELETE statements are performed. If both DELETEs succeed, the 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 error (using 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 the RAISERROR call.

Conclusion
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.

Happy Programming!

By Scott Mitchell

Thanks Scott Mitchell