Handling Errors in SQL Server Transactions

I've decided to write this post, because of misunderstanding I had about default error handling while writting T-SQL statements enclosed within single transaction.

Generally, we put our T SQL statements in a transaction, in order to perform all tasks as an atomic unit of work.

For instance:

BEGIN TRANSACTION
   INSERT INTO t2 VALUES (1);
   INSERT INTO t2 VALUES (2);
   INSERT INTO t2 VALUES (3);   
COMMIT TRANSACTION;

When we look at this block, we expect that if some statement raises a run-time error, the entire block is aborted, but it's not what actually happens.

By default, SQL Server aborts the erroneous statement only, and normally completes the rest of the statements.

I'm pretty sure, that it was not our intention :)

We have several solutions to deal with this situation:

1. Use try-catch block introduced in SQL Server 2005

BEGIN TRANSACTION
BEGIN TRY

   INSERT INTO t2 VALUES (1);
   INSERT INTO t2 VALUES (2);
   INSERT INTO t2 VALUES (3);

   COMMIT

END TRY
BEGIN CATCH
   ROLLBACK
END CATCH

2. Check @@ERROR variable after each statement and commit only when the value of @@ERROR is zero.

3. Turn on XACT_ABORT option - actually the most convenient way to achieve the desired behaviour:

SET XACT_ABORT ON

This option simply tells to SQL Server to terminate and roll back the current transaction when a run-time error occurs.

You can find the detailed description of "SET XACT_ABORT" command here.

That's it...

Mark.