Nested procedures with transactions – Education Career Blog

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?

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.