How to use SET XACT_ABORT ON the right way

Question

We have recently been parachuted to a new ETL project with very bad code. I have in my hands a query with 700 rows and all sort of update.

I would like to debug it with SET XACT_ABORT ON; and the goal is to rollback everything if only one transaction fails.

But I find several way to archive it on StackOverflow like this:

BEGIN TRANSACTION;
BEGIN TRY

-- Multiple sql statements goes here

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH

or this:

BEGIN TRY
BEGIN TRANSACTION

-- Multiple sql statements goes here

COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT(ERROR_MESSAGE())
ROLLBACK TRANSACTION
END CATCH

and none of these uses SET XACT_ABORT ON;.

I don't understand, is SET XACT_ABORT ON the same as using BEGIN TRY BEGIN TRANSACTION?

Can I just use:

SET XACT_ABORT ON;

-- Multiple sql statements goes here

and get ridof all the:

BEGIN TRANSACTION;
BEGIN TRY

?

And also, should I use BEGIN TRANSACTION and then BEGIN TRY or the other way around?

Answer 1

It is not the same. It decides when errors are thrown.

You should always use SET XACT_ABORT ON, because it is more consistent; almost always, an error will stop execution and throw an error. Else, half things throw errors and the other half continue execution.

There is a great article about this whole subject on Erland Sommarskog's site, and if you go at this point you will see a table which describes this strange behaviour. In conclusion, I recommend the General Pattern for Error Handling, as it is very well documented as well as provide you the opportunity to tweak it according to its own documentation.

Answer 2

If you have XACT_ABORT ON there is no need to manually catch any errors, unless you are doing error logging. XACT_ABORT will cause all errors to doom the transaction and roll it back.

All you need is

SET XACT_ABORT ON;
BEGIN TRAN;
--do stuff
COMMIT;

If there is only one statement then you don't even need BEGIN TRAN; and COMMIT;

Answer 3

Thank you for the resources @George Menoutis.

I post here my practical solution:

SET XACT_ABORT ON;
BEGIN TRY
      BEGIN TRANSACTION;


      -- Multiple sql statements goes here


      COMMIT TRANSACTION;
END TRY
BEGIN CATCH
      IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
      THROW;
END CATCH;
GO

SET XACT_ABORT OFF;