How to use SET XACT_ABORT ON the right way-CodePudding

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?

CodePudding user response：

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. One such tweak is the use of raiserror() which microsoft says to stop using and use throw instead; However you have to keep in mind throw() does not exist in versions prior to 2012.

CodePudding user response：

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;