How to do Error Handling for Transaction using Try Catch Block

Issue:

Some time we get requirements where we have to use transaction to do some DML operations to make sure the unit of work should be done completely. But there could be error while doing this.

So, how we can cope up with errors to achieving this?

Solution:

We can use Transaction in the Try Catch block to make sure that the transaction either successful or rollback if there is any error.

Below is an example for this:

BEGIN TRY --Start the Try Block..

BEGIN

TRANSACTION -- Start the transaction..

UPDATE

[dbo].[Table] SET salary = salary*1.1 WHERE MID = 6 --DML operations to be done in the transaction

COMMIT

TRAN -- Transaction Success!

END

TRY --End of the Try Block

BEGIN

CATCH --Start the Catch Block

IF

@@TRANCOUNT > 0 --check the error count for the errors

ROLLBACK

TRAN --RollBack in case of Error

--to raise the error message when there is an issue. Declare the variables and se the values for them

DECLARE

@ErrorMessage NVARCHAR(4000);

DECLARE

@ErrorSeverity INT;

DECLARE

@ErrorState INT;

SELECT

@ErrorMessage

= ERROR_MESSAGE(),

@ErrorSeverity

= ERROR_SEVERITY(),

@ErrorState

= ERROR_STATE();

--Raise error

RAISERROR

(@ErrorMessage, -- Message text.

@ErrorSeverity

, -- Severity.

@ErrorState

-- State.

)

END

CATCH --End of the Catch Block