XACT_ABORT is one of the least well understood options that you can configure in a SQL Server session. Yet it's very important. XACT_ABORT makes statement-terminating errors become batch-terminating errors. Without it, even within a transaction, many errors only terminate the statement that they occur in, and control passes to the next statement within the transaction, not out of the transaction.
In nearly every stored procedure that I write, the template includes the following lines:
SET XACT_ABORT ON;
SET NOCOUNT ON;
In our free SDU Tools for developers and DBAs, we added a function IsXactAbortOn to let you determine in code, if it's enabled.
You can see the outcome in the main image above.
You can see it in action here:
To become an SDU Insider and to get our free tools and eBooks, please just visit here: