In order to reuse or simplify part of code, it’s common to break down your stored procedure into multiple smaller stored procedures and call them as nested stored procedures. In most of the cases, your original stored procedure affects the content of the database. In order to make sure that the database is still consistent in case something unexpected occurs, the best solution is to use transactions and proper error handling.
Now, after the stored procedure is broken down into multiple stored procedures, you still may want to use the transactions in each stored procedure. The problem arises when a nested stored procedure rolls back the transactions. In this case, you may see the following error:
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
Why this error? Well, the problem is that every time you run “BEGIN TRANSATION”, the transaction counter increases by one. Also, every time you run “COMMIT TRANSACTION”, the transaction counter decreases by one. The “ROLLBACK TRANSACTION” works a little bit differently. When you run “ROLLBACK TRANSACTION”, the transaction counter resets back to zero no matter how many transactions you have opened.
To explain this problem in a simpler way, let’s try to reproduce it. Let’s say we have the following stored procedures where stored procedure “A” calls stored procedure “B” as in the following code:
Stored Procedure “A”
ALTER PROCEDURE [dbo].[A]
AS
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION
EXEC [dbo].[B]
COMMIT TRAN
RETURN 1
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- Throw the error message
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
RETURN 0
END CATCH
Stored Procedure “B”
ALTER PROCEDURE [dbo].[B]
AS
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION
DECLARE @TABLE AS TABLE (ID INT PRIMARY KEY)
INSERT INTO @TABLE (ID)
VALUES (1)
ROLLBACK TRANSACTION
RETURN 2
COMMIT TRANSACTION
RETURN 1
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION
-- Re-Throw the error message
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
RETURN 0
END CATCH
Let assume that for some reasons, the stored procedure “B” runs “ROLLBACK TRANSACTION” and returns. Here is what happens. As explained above, when stored procedure A runs “BEGIN TRANSATION”, the transactions counter increases to one (1). Now, when stored procedure A calls stored procedure B, the transaction counter is already set to 1. Inside the nested stored procedure B, we call another “BEGIN TRANSATION” and that sets the transaction counter to 2. Somewhere in the body of stored procedure B, for whatever reasons, we call “ROLLBACK TRANSACTION” and return. This command resets the transaction counter back to zero. But when we return back to stored procedure A, stored procedure A complains because the transaction counter was one (1) before running stored procedure B and now it is zero.
One of the solutions is it to remove transactions from the inner stored procedures. However, you may not want to leave the inner stored procedure without transactions just in case you call it directly.
Another more appropriate solution is to add one more variable in each stored procedure to check transaction count as in the following code.
Stored Procedure “A”
ALTER PROCEDURE [dbo].[A]
AS
SET NOCOUNT ON
DECLARE @BeginTran BIT = 0
BEGIN TRY
IF (@@TRANCOUNT = 0)
BEGIN
BEGIN TRANSACTION
SET @BeginTran = 1
END
EXEC [dbo].[B]
IF (@BeginTran = 1)
COMMIT TRANSACTION
RETURN 1
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0 AND @BeginTran = 1)
ROLLBACK TRAN
-- Throw the error message
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
RETURN 0
END CATCH
Stored Procedure “B”
ALTER PROCEDURE [dbo].[B]
AS
SET NOCOUNT ON
DECLARE @BeginTran BIT = 0
BEGIN TRY
IF (@@TRANCOUNT = 0)
BEGIN
BEGIN TRANSACTION
SET @BeginTran = 1
END
DECLARE @TABLE AS TABLE (ID INT PRIMARY KEY)
INSERT INTO @TABLE (ID)
VALUES (1)
IF (@BeginTran = 1)
ROLLBACK TRANSACTION
RETURN 2
IF (@BeginTran = 1)
COMMIT TRANSACTION
RETURN 1
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0 AND @BeginTran = 1)
ROLLBACK TRANSACTION
-- Re-Throw the error message
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
RETURN 0
END CATCH
As you can see above, I have added a new variable and called it “@BeginTran”. Initially this variable is zero (0). Then we check “@@TRANCOUNT” to see how many opened transactions we have. If there is no transaction, we start new transaction (BEGIN TRANSACTION) and set the “@BeginTran” variable to one (1). Next, before we run COMMIT or ROLLBACK, we check the variable to see if the transaction was started from the current procedure. If yes, we run COMMIT (or ROLLBACK). Otherwise, we skip COMMIT (or ROLLBACK). This way we prevent the above error about transaction count when we return back to the parent procedure. Also, this solution does not let the inner stored procedure run without a transaction in case the inner stored procedure is being called directly.