The information in this article applies to:
- Microsoft SQL Server, versions 4.2x, 6.0, and 6.5
SUMMARY
If a stored procedure exits with the @@trancount value that is not the same
as when the stored procedure was entered, the following error will occur:
Error: 266, Severity: 16, State: 1
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN
is missing. Previous count = %ld, Current count = %ld.
This error is for informational purposes only.
MORE INFORMATION
The following is a short code example that demonstrates the problem:
CREATE PROCEDURE test AS
SELECT @@trancount
ROLLBACK TRANSACTION
SELECT @@trancount
GO
BEGIN TRANSACTION
EXEC test
GO
Because @@trancount is not the same in both SELECT statements, error 266 is
generated on return from the stored procedure.
This is expected behavior, but it does not mean that transactions cannot be
started, completed, or aborted in a stored procedure. Instead, care must be
taken so that the @@trancount global variable matches on both entry and
exit of the stored procedure. This is documented in Transact-SQL under the
topic Reference\T\Transactions\Rollback Transaction.
This problem is more likely to occur when writing nested stored procedures.
However, there is solution so that the stored procedure works without the
error. The following is a list of solutions, with sample code for each:
- Perform final commit or rollback transactions from the same stored
procedure nesting level where the transaction began, as shown by the
following examples:
-- Example 1.a
CREATE PROCEDURE test1a AS
SELECT @@trancount
GO
BEGIN TRANSACTION
EXEC test1a
ROLLBACK TRANSACTION
GO
-- Example 1.b
CREATE PROCEDURE test1c AS
SELECT @@trancount
GO
CREATE PROCEDURE test1b AS
BEGIN TRANSACTION
EXEC test1c
COMMIT TRANSACTION
GO
EXEC test1b
GO
- If nested transactions are used in a stored procedure, perform matching
commits. Note the transaction is not committed until @@trancount is
equal to 0 (zero).
-- Example 2
CREATE PROCEDURE test2b AS
SELECT @@trancount
BEGIN TRANSACTION
SELECT @@trancount
COMMIT TRANSACTION
SELECT @@trancount
GO
CREATE PROCEDURE test2a AS
BEGIN TRANSACTION
EXEC test2b
COMMIT TRANSACTION
GO
EXEC test2a
GO
- If a rollback is needed and the stored procedure nesting level is
different than where the transaction began, use RAISERROR, with a valid
user-defined error, and check the @@error global variable after the
EXECUTE command.
-- Example 3
USE master
EXEC sp_addmessage 50001, 16, 'Rollback of transaction in test3'
GO
CREATE PROCEDURE test3 AS
RAISERROR (50001,16,1)
GO
BEGIN TRANSACTION
EXEC test3
IF @@error <> 50001
BEGIN
PRINT 'Commit'
COMMIT TRANSACTION
END
ELSE
BEGIN
PRINT 'Rollback'
ROLLBACK TRANSACTION
END
GO
- The exception to this rule is that if a trigger performs a rollback,
@@trancount needs not match its starting value, because the batch is
aborted. However, a stored procedure called by a trigger may cause the
problem if it aborted the transaction.
-- Example 4
CREATE TABLE x (col1 int)
GO
CREATE TRIGGER xins ON x FOR INSERT AS
ROLLBACK TRANSACTION
GO
CREATE PROCEDURE sp_xinsert AS
SELECT @@trancount
INSERT x (col1) VALUES (1)
SELECT @@trancount
GO
BEGIN TRANSACTION
EXEC sp_xinsert
IF @@error <> 0
BEGIN
PRINT 'Commit'
COMMIT TRANSACTION
END
ELSE
BEGIN
PRINT 'Rollback'
ROLLBACK TRANSACTION
END
GO
SELECT * FROM x