Terry Mott (9/17/2010)
Supposed ProcA begins a transaction, then executes ProcB. ProcB begins a "nested" transaction and then performs a ROLLBACK before exiting. When ProcB exits, SQL Server raises error # 266 - "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0." Here I disagree with Hugo that this is as "common sense dictates."To cover your bases, every stored procedure that might perform a ROLLBACK needs to always check that @@TRANCOUNT = 1 before rolling back. If I'm going to have to always check whether I'm in a "nested" transaction prior to rolling back, I might as well check for an open transaction prior to performing BEGIN TRAN in the first place, rather than pretend nested transactions are real in SQL Server.
Or am I missing something?
Hi Terry,
As an old-school lover of clean programming, I can only endorse this - but make sure that you also return the error condition to the calling stored procedure. If the nested procedure runs into an error, rolls back all changes, and then starts a new transaction to bump the @@TRANCOUNT back to 1, the calling procedure needs to know in some other way that it should not continue performing the rest of the actions and then committing that partial change.
The error #266 you mention, while ugly as can be, at least ensures that the calling procedure doesn't continue as if all is dandy!