Tuesday, June 24, 2008

Try...Catch and Transaction in SQL Server 2005

Level: Intermediate

Knowledge Required:
  • T-SQL
  • SQL Server 2005
  • Stored Procedure

Description:
In Stored Procedure (having lots of INSERTs, UPDATESs and DELETEs) we use Transaction (Begin Tran, Commit Tran, RollBack Tran). We usually begin with Begin Tran and on some error we do RollBack Tran and if NO error occurred we Commit Tran.

A better way to do this task is to use the Try...Catch in SQL Server 2005. For example:
    Begin Try
BEGIN TRAN;

INSERT INTO ....

SELECT ...

INSERT INTO ...

DELETE ...

DELETE ...

INSERT ...

COMMIT TRAN;
End Try
Begin Catch
ROLLBACK TRAN;

DECLARE @ErrorMsg varchar(max);
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;

SET @ErrorMsg = ERROR_Message();
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();

RAISERROR(@ErrorMsg, @ErrorSeverity, @ErrorState);
End Catch


As you can see,

1) We start the Transaction in Try...Catch block
2) Next we move forward and try to do insert, update and delete
3) If any error occurred during these statements the Exception will be caught in the Begin Catch...End Catch block
4) Then in this block we have first Rolled Back the Transaction
5) Then again throw the exception so the caller should know that some error was occurred during processing
6) And if NO exception occured then finally before the End Try we have Committed the Transaction

3 comments:

dorogoj said...

If in try block we have some logic and need use Raiserror function we have to use Severity higer when 10.
A severity of 10 or lower return a message without invoking the CATCH block.

Unknown said...

This can fail if ERROR_STATE() returns 0

Ali Kazak said...

thank you so much.