0

As far as I know if you open an SQL transaction and an error occurs within it it will rollback automatically.

So, is there any point to putting a transaction in a try-catch block like shown here or here.

Furthermore would set xact_abort on render the try-catch block obsolete in any case?

J. Doe
  • 363
  • 2
  • 11
  • Unfortunately, "if an error occurs it will rollback automatically" is absolutely not always true. Whether or not a rollback will happen depends on the kind of error, and whether any more statements will be executed (despite the transaction already being doomed) likewise depends. See [Erland Sommarskog's excellent writeup on these matters](http://sommarskog.se/error_handling/Part1.html). `SET XACT_ABORT ON` is often very helpful to simplify error handling, as is managing the transaction client-side (since there it typically is a hard guarantee that any error produces a rollback). – Jeroen Mostert Jan 16 '20 at 14:46
  • Yes, I would keep the try-catch and rollback the transaction in the catch block. Guaranteed to rollback if there is an error and you can raise an error in the catch block. – Wesley Nightingale Jan 16 '20 at 15:08
  • @WesleyNightingale, does `set xact_abort on` do the same thing as the try-catch block in this case? – J. Doe Jan 16 '20 at 15:42
  • @JeroenMostert, thanks, but does that mean that using `set xact_abort on` is the same as a try-catch in this case? – J. Doe Jan 16 '20 at 15:43
  • 2
    No. *Most* errors that happen under `XACT_ABORT ON` rollback (certainly more than under `OFF`), but not all. See [here](http://sommarskog.se/error_handling/Part2.html#classification) (still from Erland's articles) for the details. Best results are obtained using *both* `XACT_ABORT ON` *and* `TRY .. CATCH`. – Jeroen Mostert Jan 16 '20 at 15:47
  • 2
    You can also do different things in the catch block (log to a table with specific info, exec sp, etc.) – scottsaenz Jan 16 '20 at 20:32

0 Answers0