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?
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.