Using TRY - CATCH to Rollback a Transaction - SQL Server

Try Catch Blocks for handling SQL Server exceptions were introduced in 2005. Here is a nice code sample demonstrating how to use them to catch exceptions and either rollback or commit a transaction.

The Feature - “TRY...CATCH” 

The Most impressive functionality improvement added for developers is “Exceptional Handling” technique. There is no beneficial reason if you are not writing your code in “Try.. Catch” block.


  • A TRY Block - the TRY block contains the code / script 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.

Checking @@ERROR - the “sql 2000” Way of Handling Errors in Stored Procedure

CREATE PROC usp_AccountTransaction

@AccountNum INT,

@Amount DECIMAL

AS

BEGIN

BEGIN TRANSACTION --beginning a transaction..

UPDATE MyChecking SET Amount = Amount - @Amount

WHERE AccountNum = @AccountNum

IF @@ERROR != 0 --check @@ERROR variable after each DML statements..

BEGIN

ROLLBACK TRANSACTION --RollBack Transaction if Error..

RETURN

END

ELSE

BEGIN

UPDATE MySavings SET Amount = Amount + @Amount

WHERE AccountNum = @AccountNum

IF @@ERROR != 0 --check @@ERROR variable after each DML statements..

BEGIN

ROLLBACK TRANSACTION --RollBack Transaction if Error..

RETURN

END

ELSE

BEGIN

COMMIT TRANSACTION --finally, Commit the transaction if Success..

RETURN

END

END

END

GO

Yes!.. This is what we used to code a Stored Procedure in Sql 2000; Check for @@ERROR after every DML (Data Manipulation) Statements and Commit / RollBack the transaction.


While working with SQL Server 2000, detecting errors could only be handled by checking a global error variable, @@ERROR. Because the
@@ERROR variable value is reset after each SQL statement, this 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. And yes, SQL Server 2005 still supports to @@ERROR Approach. 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. Lets move on to it! 

Handling Errors With SQL Server 2005's TRY...CATCH Blocks

In Fact, there is really nothing new to be describe and discuss on TRY...CATCH Block; as we all know with any programming languages, 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.

Basic Syntax is,       

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

BEGIN TRY

SELECT GETDATE()

SELECT 1/0--Evergreen divide by zero example!

END TRY

BEGIN CATCH

SELECT 'There was an error! ' + ERROR_MESSAGE()

RETURN

END CATCH;

Using TRY...CATCH to Rollback a Transaction in the Face of an Error 

As you saw in earlier example, one of the downsides of the @@ERROR variable approach is that to implement Transaction; we must check this variable after each and every DML 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.

Lets Alter the Previous Example!

ALTER PROC usp_AccountTransaction

@AccountNum INT,

@Amount DECIMAL

AS

BEGIN

BEGIN TRY --Start the Try Block..

BEGIN TRANSACTION -- Start the transaction..

UPDATE MyChecking SET Amount = Amount - @Amount

WHERE AccountNum = @AccountNum

UPDATE MySavings SET Amount = Amount + @Amount

WHERE AccountNum = @AccountNum

COMMIT TRAN -- Transaction Success!

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

ROLLBACK TRAN --RollBack in case of Error

-- you can Raise ERROR with RAISEERROR() Statement including the details of the exception

RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), 1)

END CATCH

END

GO

Just look at the simplicity and line of code than previous example!

In the TRY block a transaction is started and the two UPDATE statements are performed. If both UPDATEs succeed, the COMMIT will be reached and the transaction committed. If, however, either one produces an error, control will be execute CATCH block where the transaction will be rolled back.

Also, you can “re-raises” the error (using RAISERROR) so that the error information will be passed up to your .Net application from where you are calling the Stored Procedure, in case if you want to use the error information to process further steps anyhow.

Thats it. lets Code Better!

Referenced Links

Other Functions / Statements Reffered

@@ERROR - Returns the error number for the last Transact-SQL statement executed. Returns 0 if the previous Transact-SQL statement encountered no errors. @@ERROR is cleared and reset on each statement executed

RAISEERROR() - Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.

@@TRANCOUNT - The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1

By kaushal parik   Popularity  (49953 Views)
Biography - kaushal parik
Microsoft MVP [.NET/ASP.NET 2008-09]