SQL: Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing.

The following error message is got when the number of open transactions is different than the number of committed or rolled back transaction in the end of the stored procedure.

The global variable @@TRANCOUNT gives the number of opened transactions in the moment of check-up. If you are not sure what is number of opened transactions you can check the number at the end of the procedure and commit or rollback.

DECLARE @TranStarted bit
SET @TranStarted = 0

IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0

-- do something
-- use select statements with locking rows or tables using
-- WITH ( UPDLOCK, HOLDLOCK ), WITH (HOLDLOCK)

IF( @@ERROR <> 0 )
GOTO Cleanup

--do something


IF( @@ERROR <> 0 )
GOTO Cleanup


-- if no errors commit the transaction
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END

RETURN 1

-- in case of errors jump to the label cleanup to rollback the transaction
Cleanup:

IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END

RETURN 0