Search This Blog

Using TRY...CATCH to Rollback a Transaction in case of an error

As you all might know, one of the downsides of the @@ERROR variable approach  to catch the  error is that we must check the value of this variable after each and every DML /DDL  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.

 

BEGIN TRAN
BEGIN TRY
    SELECT  1 / 0 infinite
    COMMIT TRAN
END TRY
BEGIN CATCH
    RAISERROR ( 'err', 16, 1 )
    SELECT  @@trancount before
    ROLLBACK TRAN
    SELECT  @@trancount after
END CATCH
GO 


Does the order in which we put BEGIN TRAN..BEGIN TRY matters , let us check



 



BEGIN TRY 
BEGIN TRAN 
    SELECT  1 / 0 infinite
COMMIT TRAN
END TRY
BEGIN CATCH
    RAISERROR ( 'err', 16, 1 )
    SELECT  @@trancount before
    ROLLBACK TRAN
    SELECT  @@trancount after
END CATCH
GO 	


No comments: