Post reply

Will nested SP commit data when CALLING Proc fails

  • March 13, 2007 at 11:11 am

    #176518

    I have Stored Proc A  with a BEGIN TRAN A... 

    Inside BEGIN TRAN A, I Exec Stored Proc B  (w/ now BEGIN/END in Stored Proc B)

    When Stored Proc B is successful and returns control back to Stored Proc A .. and Stored Proc A executes a few more command inside the original BEGIN TRAN A -- and FAILS...

    ... wll Stored Proc B roll back?

    BT

  • March 13, 2007 at 12:24 pm

    #694783

    I had problems with a similar scenario & ultimately had to put everything in a single tran.  You can use @@TRANCOUNT to determine the nesting level but in the end, if you COMMIT/ROLLBACK the outer transaction, all inner transactions are supposed to follow suit.

    It seems that the only effect nested transactions have is to increment/decrement @@TRANCOUNT.

Viewing 2 posts - 1 through 1 (of 1 total)

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