rollback transaction in nested stored proc

  • create proc proc1 (@param1 int)

    as

    begin try

    declare @param2 int

    begin transaction

    exec proc2 @param2

    commit transaction

    end try

    begin catch

    if @@trancount > 0

    rollback transaction

    end catch

    i haven't had an opportunity to do this before. I have nested stored proc and both inserts values into different tables. To maintain atomicity i want to be able to rollback everything if an error occurs in the inner or outer stored procedure.

    Found a couple articles on the net but nothing really suiting my requirement.Please suggest. Thanks.

  • Any rollback rolls back all the nested transactions.

    Gerald Britton, Pluralsight courses

  • gravitysucks (11/24/2014)


    create proc proc1 (@param1 int)

    as

    begin try

    declare @param2 int

    begin transaction

    exec proc2 @param2

    commit transaction

    end try

    begin catch

    if @@trancount > 0

    rollback transaction

    end catch

    i haven't had an opportunity to do this before. I have nested stored proc and both inserts values into different tables. To maintain atomicity i want to be able to rollback everything if an error occurs in the inner or outer stored procedure.

    Found a couple articles on the net but nothing really suiting my requirement.Please suggest. Thanks.

    Quick note, a rollback in a "nested" transaction will rollback all the transactions. There is in fact no such thing as a nested transaction, simply a counter that's iterated. Consider this code sample

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    SELECT @@TRANCOUNT AS TRANCOUNT;

    BEGIN TRAN

    BEGIN TRAN

    BEGIN TRAN

    BEGIN TRAN

    SELECT @@TRANCOUNT AS TRANCOUNT;

    ROLLBACK TRAN

    SELECT @@TRANCOUNT AS TRANCOUNT;

    Output

    TRANCOUNT

    -----------

    0

    TRANCOUNT

    -----------

    4

    TRANCOUNT

    -----------

    0

  • Eirikur Eiriksson (11/24/2014)


    gravitysucks (11/24/2014)


    create proc proc1 (@param1 int)

    as

    begin try

    declare @param2 int

    begin transaction

    exec proc2 @param2

    commit transaction

    end try

    begin catch

    if @@trancount > 0

    rollback transaction

    end catch

    i haven't had an opportunity to do this before. I have nested stored proc and both inserts values into different tables. To maintain atomicity i want to be able to rollback everything if an error occurs in the inner or outer stored procedure.

    Found a couple articles on the net but nothing really suiting my requirement.Please suggest. Thanks.

    Quick note, a rollback in a "nested" transaction will rollback all the transactions. There is in fact no such thing as a nested transaction, simply a counter that's iterated. Consider this code sample

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    SELECT @@TRANCOUNT AS TRANCOUNT;

    BEGIN TRAN

    BEGIN TRAN

    BEGIN TRAN

    BEGIN TRAN

    SELECT @@TRANCOUNT AS TRANCOUNT;

    ROLLBACK TRAN

    SELECT @@TRANCOUNT AS TRANCOUNT;

    Output

    TRANCOUNT

    -----------

    0

    TRANCOUNT

    -----------

    4

    TRANCOUNT

    -----------

    0

    Thank you. I should have tried that before prior posting the question. The articles on the net i read made it sound like it was not as simple as that. I will give it a try.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply