Microsoft® SQL Server 2008 R2 Unleashed

Calling Stored Procedures from Transactions

Stored procedures can be called from within a transaction, and they can also initiate transactions. SQL Server notes the transaction nesting level, which is available from the @@trancount function, before calling a stored procedure. If the value of @@trancount when the procedure returns is different from the value of @@trancount when it was executed, SQL Server displays error message 266: Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing. This message indicates that transaction nesting is out of balance. Because a stored procedure does not abort the batch on a rollback transaction statement, a rollback transaction statement inside the procedure could result in a loss ...