What is the effect of having XACT_ABORT on/off in parent/child stored procedures respectively?
error-handling, t-sql, xact-abort
Solution
As per Andomar's answer here and MSDN:
The setting of SET XACT_ABORT is set at execute or run time and not at parse time
i.e. `XACT_ABORT` will not be 'copied' from the creation session to each procedure, so any PROC which doesn't explicitly set this option internally will inherit the setting from the ambient session at run time, which can be disastrous.
FWIW, as a general rule, we always ensure that `XACT_ABORT` is ON globally and do a lint check to ensure none of our PROCs have overridden this setting.
Note that `XACT_ABORT` isn't a silver bullet, however - e.g. errors that have been raised by your PROC with RAISERROR won't terminate the batch. However, it seems that this is improved with the THROW keyword in SQL 2012
As you've suggested, and as per Remus Rusanu's observation, structured exception handling (TRY / CATCH) is a much more clean and robust mechanism for handling of exceptions.
Problem
I'm trying to improve the error handling of a current system to produce more meaningful error messages. I have a "root" stored procedure that makes several calls to other nested stored procedures. In the root sp, `XACT_ABORT` is set to `ON` but in the nested procedures, `XACT_ABORT` is set to `OFF`. I want to capture the specific errors from the lower level procedures rather than getting the root procedure's error. I often see the error, `uncommittable transaction is detected at the end of the batch, the transaction is being rolled back.` Is there any effect to having these "mixed" environments with the `XACT_ABORTs`? Also, if you have any suggestions for advanced error handling, that would be much appreciated. I think I would like to use `sp_executesql` so I can pass parameters to get error output without having to modify all of the stored procedures and use `RAISERROR` to invoke the parent procedure's `CATCH` block.