Nested procedures with transactions

Question

Hi!

I have the following problem. I have to stored procedures (debug messages double-indended):

CREATE PROC innerProc
AS 
BEGIN
        SELECT 'innerProc 1',@@TRANCOUNT
    BEGIN TRAN
        SELECT 'innerProc 2',@@TRANCOUNT
    ROLLBACK
        SELECT 'innerProc 3',@@TRANCOUNT
END

GO  -----------------------------------------

CREATE PROC outerProc
AS 
BEGIN
        SELECT 'outerProc 1',@@TRANCOUNT
    BEGIN TRAN
        SELECT 'outerProc 2',@@TRANCOUNT
    EXEC innerProc
        SELECT 'outerProc 3',@@TRANCOUNT
    ROLLBACK
        SELECT 'outerProc 4',@@TRANCOUNT
END

GO  -----------------------------------------

EXEC outerProc

What they do?

1. outerProc begins transaction (@@TRANCOUNT = 1)
2. executes innerProc (@@TRANCOUNT at the beginning of the proc = 1)
3. innerProc begins another transaction (@@TRANCOUNT = 2)
4. innerProc rollbacks transaction (@@TRANCOUNT = 0)
5. AND HERE IS THE PROBLEM: @@TRANCOUNT at the beginning of the innerProc is not equal to @@TRANCOUNT at the end. What am I doing wrong? Is it correct approach?

Answer 1

I believe you need to use named transactions or else you're killing all transactions when you rollback on the nested one, even if it is scoped to just the inner sproc

http://msdn.microsoft.com/en-us/library/ms188929.aspx

Further reading: http://msdn.microsoft.com/en-us/library/ms181299.aspx

ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement. In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0. ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT.