What is the effect of having XACT_ABORT on/off in parent/child stored procedures respectively?

Question

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.

Konrad

Answer 1

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.

Califate

Thanks, Stuart. If I have XACT_ABORT ON in all procedures, I'm assuming that is going to affect the error handling in the procedures where it is currently set to OFF, yes? Also, what is the best method for error handling with XACT_ABORT ON in order to get specific error messages, since any code following the line that produced the error never gets executed? I believe the CATCH block supersedes XACT_ABORT, so would I have to do any error handling in there and then ROLLBACK? – Konrad

The end goal here is to produce error messages from the actual procedure in which the error occurred, and not just let it bubble up where the actual error message could be changed. – Konrad

Ideally IMO SQL structured exception handling should behave like other languages,i.e. don't catch an exception if you don't intend handling it. Sadly, SQL doesn't have a FINALLY clause for cleanup. This is mitigated somewhat e.g. XACT_ABORT cleans up transactions, and things like #temp tables don't need to be explicitly dropped, I've found only a few instances where I need to catch only for the sake of cleanup (e.g. cursors). One disclaimer - I'm more of a .NET App developer so avoid heavy nesting of SPROCs, so I tend to doing more work in the App (e.g. using TransactionScopes etc) – Califate

Answer 2

A way to keep XACT_ABORT on and get errors if any or commit if all is fine when calling SP that may call other SP: two sp and three tests as example

 create PROCEDURE [dbo].[myTestProcCalled]
    (
        @testin int=0
    )
    as
    begin

        declare @InnerTrans int 
        set XACT_ABORT on;



        set @InnerTrans = @@trancount;

        PRINT '02_01_Trancount='+cast (@InnerTrans as varchar(2));

        begin try
           if (@InnerTrans = 0)
           begin
                PRINT '02_02_beginning trans';
                begin transaction
           end 

           declare @t2 int
           set @t2=0
           PRINT '02_03_doing division'
           set @t2=10/@testin 

           PRINT '02_04_doing AfterStuff'
           if (@InnerTrans = 0 and XACT_STATE()=1)
             begin
                 PRINT '02_05_Committing'

                commit transaction
              end
              PRINT '02_05B_selecting calledValue=' +cast(@t2 as varchar(20))
              select  @t2 as insidevalue

        end try

        begin catch
         PRINT '02_06_Catching Errors from called'
              declare @ErrorMessage nvarchar(4000);
            declare @ErrorNumber int;
            declare @ErrorSeverity int;
            declare @ErrorState int;

            select @ErrorMessage = error_message(), @ErrorNumber = error_number(), @ErrorSeverity = error_severity(), @ErrorState = error_state();

            if (@InnerTrans = 0 and XACT_STATE()=-1)
            begin
              PRINT '02_07_Rolbacking'
              rollback transaction
            end
           PRINT '02_08_Rising Error'
            raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState); 
            --use throw if in 2012 or above 
            -- else might add a "return" statement
        end catch

    end
    go

    create PROCEDURE [dbo].[myTestPCalling]
    (
        @test int=0
        ,@testinside int=0
    )
    as
    begin

        declare @InnerTrans int 
        set XACT_ABORT on;



        set @InnerTrans = @@trancount;

        PRINT '01_01_Trancount='+cast (@InnerTrans as varchar(2));

        begin try
           if (@InnerTrans = 0)
           begin
                PRINT '01_02_beginning trans';
                begin transaction
           end 

           declare @t2 int
           set @t2=0
           PRINT '01_03_doing division'
           set @t2=10/@test 
           PRINT '01_04_calling inside sp'
           execute [dbo].[myTestProcCalled]
                  @testin  = @testinside
           --
           PRINT '01_05_doing AfterStuff'
           if (@InnerTrans = 0 and XACT_STATE()=1)
             begin
                 PRINT '01_06_Committing'
                 commit transaction
                 PRINT '01_06B_selecting callerValue=' +cast(@t2 as varchar(20))
              select  @t2 as outsidevalue

              end
        end try

        begin catch
         PRINT '01_07_Catching Errors from Caller'
            declare @ErrorMessage nvarchar(4000);
            declare @ErrorNumber int;
            declare @ErrorSeverity int;
            declare @ErrorState int;

            select @ErrorMessage = error_message(), @ErrorNumber = error_number(), @ErrorSeverity = error_severity(), @ErrorState = error_state();
            if (@InnerTrans = 0 and XACT_STATE()=-1)
            begin
              PRINT '01_08_Rolbacking'
              rollback transaction
            end
            PRINT '01_09_Rising Error'
            raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState); 
            --use throw if in 2012 or above 
            -- else might add a "return" statement
        end catch

    end

----test 1 :result OK----
USE [PRO-CGWEB]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[myTestPCalling]
        @test =2
        ,@testinside = 2

SELECT  'Return Value' = @return_value

GO

----test2 :error in caller ----
USE [PRO-CGWEB]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[myTestPCalling]
        @test =0
        ,@testinside = 2

SELECT  'Return Value' = @return_value

GO

----test3 :error in calling ----
USE [PRO-CGWEB]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[myTestPCalling]
        @test =2
        ,@testinside = 0

SELECT  'Return Value' = @return_value

GO

Lohner