Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

In this article, we will discuss an error “Msg 266: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements.” that I faced when I was working with the SQL Server transactions and stored procedures. I will describe how I encountered the issue. Also, I will explain the cause and solution of this error.

Problem

Recently, I was working with SQL Server transactions and was trying to implement these with stored procedures. Here is what I was doing:

USE [master]
GO
ALTER PROCEDURE [dbo].[InsertIntoPersons] @FirstName nchar(10), @LastName nchar(10),
	@Age smallint, @Gender nchar(7), @Email nchar(30)='N/A', @Phone nchar(20)= 'N/A', @Location nchar(20)
AS
BEGIN
	BEGIN TRY
		INSERT INTO dbo.Phones( Phone, [First Name], [Last Name])
		VALUES( @Phone, @FirstName, @LastName)
		INSERT INTO dbo.Persons(
		[First Name], [Last Name], Age, Gender, Email, Phone, Location)
		VALUES( @FirstName, @LastName, @Age, @Gender, @Email, @Phone, @Location)
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
		SELECT  
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
	END CATCH
END

USE master
GO
BEGIN TRANSACTION
EXEC dbo.InsertIntoPersons 'Kate', 'Lincon', 45, 'Female', NULL, '+1 540 034 735', 'Reston'

• As you can observe in the above code, I was trying to insert data into two tables.
• In case of any exception in the second insert statement, the whole transaction was supposed to be rolled back.
• Everything worked fine, i.e. the transaction was rolled back, but I faced this error:

Cause

I researched about this error and found my mistake. When we work with transactions in SQL Server, the transactional statements are counted for a particular stored procedure. The number of BEGIN TRANSACTION and the COMMIT TRANSACTION statements are tracked and their numbers should match each other.

For example, If a procedure has 5 BEGIN TRANSACTION statements then it is also supposed to have 5 COMMIT TRANSACTION statements.

Then I found my mistake. I initiated the transaction outside the stored procedure and the COMMIT statement and the ROLLBACK statements were inside the stored procedure.

Transaction count after execute indicates a mismatching number of begin and commit statements

The solution to this problem was to use the BEGIN TRANSACTION statement inside the stored procedure where I wanted to use the COMMIT TRANSACTION or the ROLLBACK transaction. The new SQL Code was:

USE [master]
GO
ALTER PROCEDURE [dbo].[InsertIntoPersons] @FirstName nchar(10), @LastName nchar(10),
	@Age smallint, @Gender nchar(7), @Email nchar(30)='N/A', @Phone nchar(20)= 'N/A', @Location nchar(20)
AS
BEGIN
	BEGIN TRANSACTION
		BEGIN TRY
			INSERT INTO dbo.Phones( Phone, [First Name], [Last Name])
			VALUES( @Phone, @FirstName, @LastName)
			INSERT INTO dbo.Persons(
			[First Name], [Last Name], Age, Gender, Email, Phone, Location)
			VALUES( @FirstName, @LastName, @Age, @Gender, @Email, @Phone, @Location)
			COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
		SELECT  
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
	END CATCH
END

USE master
GO
EXEC dbo.InsertIntoPersons 'Kate', 'Lincon', 45, 'Female', NULL, '+1 540 034 735', 'Reston'

You may like the following SQL server articles:

• How to select latest record in SQL Server
• SQL Server stored procedure parameters
• Msg 11555 NOT NULL parameters are only supported with natively compiled modules, except for inline table-valued functions
• Try catch in SQL Server stored procedure with examples
• SQL Server stored procedure insert into with examples
• Msg 567: Unable to retrieve data for this section of the report

I hope you learned something from my experience. Also, you might have the reason behind the error “Msg 266: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements.“