Usage of Try Catch in SQL SERVER 2005 for Rollback a Transaction

Implements error handling for Transact-SQL that is similar to the exception handling in the Microsoft Visual C# and Microsoft Visual C++ languages. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a 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 theRAISERROR 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!