What is the point of TRY CATCH block when XACT_ABORT is turned ON?

Code sample with XACT_ABORT_ON:

SET XACT_ABORT_ON;

BEGIN TRY
  BEGIN TRANSACTION
    //do multiple lines of sql here
  COMMIT TRANSACTION
END TRY

BEGIN CATCH
  IF (@@TRANCOUNT > 0) ROLLBACK;
  //may be print/log/throw error
END CATCH

Since XACT ABORT is ON, any error will automatically rollback the transaction. So what purpose does the TRY CATCH block serve?

You are right that it is not necessary to catch errors you are not intending on handling. SET XACT_ABORT ON; ensures a rollback in all circumstances (except for a couple of very weird edge cases of uncatchable errors, which Erland Sommarskog says are basically unfixed bugs). Syntax errors from dynamic SQL are also not caught and rolled back, however that is just one more good reason to use a good IDE, proper version control and avoid dynamic SQL.

In my opinion, it is only necessary to CATCH errors if you intend on dealing with them. Erland's articles are generally misunderstood, they are intended for handling errors, not just catching and re-throwing.

SET XACT_ABORT ON; is always necessary, in order to correctly roll back transactions.

And in triggers, you must never explicitly roll back. If you do, you will get a spurious error # 3609 The transaction ended in the trigger. The batch has been aborted. And XACT_ABORT is ON by default in triggers.

But there are sometimes circumstances when you do actually want to catch and handle errors within your SQL code. For this you must use BEGIN TRY BEGIN CATCH, and you also must use a conditional ROLLBACK as shown.

For example, you can see in this fiddle that a second insert outside of the transaction is still committed, even though XACT_ABORT was ON, because BEGIN CATCH was used.

TL;DR;

You only need to use BEGIN CATCH and conditional ROLLBACK; if handling the error.

SET XACT_ABORT must always be ON if you have an explicit transaction, irrespective of whether there is a CATCH, to ensure that rollback happens correctly.

StackExchange Database Administrators Q#306846, answer score: 20