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.