• Nested transactions are a lie (and a major pain)

    The rollback in the inner procedure rolls back all the way to the first begin tran

    Hence you enter the inner proc with a transaction open (tran count 1), start a second one (tran count now 2) hit rollback, that rolls back all open transactions (tran count 0, different from when you entered the proc), return to the outer procedure and hit commit, but there's no open transaction any longer.

    Don't nest transactions (unless you really know what you're doing and how nested transactions work). Either do your transaction management in the outer proc or in the inner proc, not both.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass