Hi,
I am having an Storedprocedure with another sp calling from it. Both outer
and inner sp has transaction.
In the inner sp,it has a failure condition and rollback transaction with certain input.
Now,getting message as
"Transaction count after EXECUTE indicates a mismatching number of BEGIN and
COMMIT statements. Previous count = 1, current count = 0."
When i checked the transcount with @@trancount,it was one before entering inner sp and
becomes zero when rollback.
There is no try catch used.
Any help to eliminate this error will be helpful.
Thanks
We do the following to prevent leaving a nested Sproc with @@TRANCOUNT set to zero (by a ROLLBACK)
BEGIN TRANSACTION MyTransactionBlockLabel_01
SAVE TRANSACTION MyTransactionBlockLabel_02
SET @ResultCode = 0 -- Assume No Error
... Code within Transaction Block goes here ...
... Set @ResultCode to non-zero value if errors encountered
... Use GOTO MyTransactionBlockLabel_END to bypass subsequent code if appropriate
MyTransactionBlockLabel_END:
IF @ResultCode = 0
BEGIN
COMMIT TRANSACTION MyTransactionBlockLabel_01
END
ELSE
BEGIN
ROLLBACK TRANSACTION MyTransactionBlockLabel_02
COMMIT TRANSACTION MyTransactionBlockLabel_01
END