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.“
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.