SQLTeam.com | Weblogs | Forums

Nested transaction


#1

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


#2

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