Nested Stored Procedure with Begin Transaction and Rollback

Nested Stored Procedure with Begin Transaction and Rollback: It is mentionned in the SQL Documentation that a Rollback is full rollback (all levels) are rolled back, in contrast to the commit that only commits the current level. you can't execute a rollback anywhere other than the outermost transaction if you want to avoid errors. BOL says:Naming multiple transactions in a series of nested transactions with a transaction name has little effect on the transaction. Only the first (outermost) transaction name is registered with the system. A rollback to any other name (other than a valid savepoint name) generates an error. None of the statements executed before the rollback are in fact rolled back at the time this error occurs. The statements are rolled back only when the outer transaction is rolled back.

When you use nested sp with transactions, that is you using nested transaction. When use nested transactions without name it, when you call rollback tran, it will rollback the outer most transaction. So it set @@TranCount to 0. You can try the code below to test:

begin tran
print @@trancount
begin tran
print @@trancount
begin tran
print @@trancount
commit tran
print @@trancount
begin tran
print @@trancount
rollback tran
print @@trancount

You can't rollback (but commit OK) nested (inner) transaction with name. Because only the first (outermost) transaction is registered with the system. A rollback to any other name (other than a valid savepoint name) generates an error.
Test this code:begin tran T1
print @@trancount
begin tran T2
print @@trancount
begin tran T3print @@trancount
commit tran T3 --OK
print @@trancount
rollback tran T2 --Error
print @@trancount
So, if you want to rollback at inner trans, use :
SAVE TRANSACTION MyName IF (@@error <> 0)BEGIN ROLLBACK TRANSACTION MyNameEND