Sql-server – Nested stored procedures with use of transactions

sql servert-sql

I have a question regarding this tsql-code found in this thread regarding "nested transactions" (unfortunately my reputation is not large enough to comment in that thread):
In what cases a transaction can be committed from inside the CATCH block when XACT_ABORT is set to ON?

Here is a procedure with transactions-check and a savepoint if the @@trancount > 0
But I don't see the difference the savepoint makes, when there is a "raiserror-statement" in the catch block of the procedure. Will this not force the calling code to rollback to the beginning of the calling code, thus ignoring the savepoint?

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Insert into table:
           INSERT INTO TABLE1 'Data from usp_my_procedure_name'

        -- Make an error:
           SELECT 1/0

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end
go

If this procedure is called in a nested way like this

CREATE OuterProc AS
    BEGIN TRY
     BEGIN TRANSACTION

     INSERT INTO TABLE1 'Data from OuterProc code'

     EXEC usp_my_procedure_name 

     COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
     ROLLBACK
    END CATCH
END

If I execute the OuterProc, the data 'Data from OuterProc code' will not get inserted.
Then what is the point of the savepoint?

It works if I remove the raiserror-part of the code. Then the 'Data from OuterProc code' gets inserted..

So my question is – what is the point of the savepoint when there is a raiserror-statement in the procedures catch-block?

Best Answer

Let's first get the RAISERRROR() part out of the way, more specifically why does the insert happen when it is removed.

It works if I remove the raiserror-part of the code. Then the 'Data from OuterProc code' gets inserted..

The raise error is defined outside of the TRY block scope 

    raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;

And the documentation shows that the error is returned to the caller (OuterProc) when RAISERROR() is ran outside of the try block scope.

The error is returned to the caller if RAISERROR is run:

Outside the scope of any TRY block.

Source

As a result, the raiserror returns control to the OuterProc procedure, and as a result of the error happening in the TRY block of the outer procedure it is returned to the CATCH block. And, inside this catch block is a ROLLBACK.

--> ROLLBACK

If you add this to the CATCH block of the outer proc:

BEGIN CATCH
    declare @error int, @message varchar(4000), @xstate int;
    select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();

raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
 ROLLBACK
END CATCH

The error gets re-raised:

Msg 50000, Level 16, State 1, Procedure OuterProc, Line 17 [Batch Start Line 58] usp_my_procedure_name: 50000: usp_my_procedure_name: 8134: Divide by zero error encountered.

And the outer proc insert:

 INSERT INTO TABLE1 
 VALUES('Data from OuterProc code')

Gets rolled back.

So my question is - what is the point of the savepoint when there is a raiserror-statement in the procedures catch-block

The savepoint is there to not rollback the entire transaction, but due to your use of TRY CATCH in the outer procedure, your outer procedure is also rolled back.

If you don't want the outer transaction to also be rolled back, then you should not use an additional TRY CATCH block around the outer transaction.

You would have to define the procedure like this:

CREATE PROCEDURE OuterProc2 AS

     BEGIN TRANSACTION

     INSERT INTO TABLE1 
     VALUES('Data from OuterProc code')

     EXEC usp_my_procedure_name 

     COMMIT TRANSACTION

If you then run the procedure: 

EXEC OuterProc2;

The inner procedure error is returned to the caller:

Msg 50000, Level 16, State 1, Procedure usp_my_procedure_name, Line 36 [Batch Start Line 54] usp_my_procedure_name: 8134: Divide by zero error encountered.

But the Data from OuterProc code string is still inserted.

SELECT * 
FROM table1;

Value
Data from OuterProc code

If you do not use a savepoint, then the entire transaction will be rolled back. As a result, errors will occur when trying to COMMIT the non existing tran.

Msg 266, Level 16, State 2, Procedure usp_my_procedure_name, Line 0 [Batch Start Line 53] Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0. Msg 3902, Level 16, State 1, Procedure OuterProc2, Line 10 [Batch Start Line 53] The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Related Question