Rollback Nested Transactions in Stored Procedure - SQL Server

In a previous article Rollback Transaction in SQL Server , l explained how to use a Try..Catch block to commit and rollback transaction in stored procedure. In this article, we will use the same example to see how to rollback nested transactions in Stored Procedures.

CREATE TABLE TT (num int)
GO
INSERT INTO TT(num) VALUES (50)
GO

-- Code from SQLServerCurry.com
-- Create First Proc with Transactions
CREATE PROC SP1
AS
BEGIN TRY
BEGIN TRAN
INSERT INTO TT(num) VALUES (100)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
GO

-- Create Second Proc with Transactions
-- Raise an Error in this Proc
CREATE PROC SP2
AS
BEGIN TRY
BEGIN TRAN
INSERT INTO TT(num) VALUES (200)
RAISERROR('Manually raised error', 17, 1)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
GO

-- Create Main Procedure
CREATE PROC MainProc
AS
BEGIN
BEGIN TRY
BEGIN TRAN
INSERT INTO TT(num) VALUES (300)
EXEC SP1 -- Execute StoredProc 1
EXEC SP2 -- Execute StoredProc 2
COMMIT TRAN
END TRY
BEGIN CATCH
SELECT @@trancount
IF @@trancount > 0 ROLLBACK TRAN
-- Error Message
--DECLARE @Err nvarchar(1000)
--SET @Err = ERROR_MESSAGE()
RAISERROR ('Error in Proc',16,1)
END CATCH
END

GO

EXEC MainProc

SELECT * FROM TT

DROP TABLE TT
DROP PROC SP1
DROP PROC SP2
DROP PROC MAINProc

Shown above are two Stored Procedures (SP1 and SP2) with transaction processing enabled, nested within another stored procedure (Main Proc) which also has transaction processing. This leads to nested transaction. The proc SP2 manually raises an error, causing its transaction to rollback. On running the code above, you will get the following error

which shows that an error in the nested stored procedure, causes a rollback, which in turn will roll back everything. Doing a SELECT * FROM TT shows that no new rows were inserted in the table.

Points to consider:

• When you are using nested transactions, you must execute a COMMIT TRAN statement for each BEGIN TRAN statement issued, for the transaction to complete successfully
• Although I haven’t done so, in complex nested transactions, you can check the value of @@trancount to see if it is active, before using another BEGIN TRAN. If it is active, you can use SAVE TRAN instead. If @@trancount is 0, you are no more in a transaction. You can print the value of @@@trancount in these stored procedures to see how its value changes.
• If you are using SAVEPOINTS, I saw a very interesting point about nested transactions discussed in this forum. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

Note 1: Although the code example shown above runs, what I was originally attempting is to do is access the ERROR_MESSAGE() in the main procedure

However when I tried so, SQL Server fired an error

Msg 50000, Level 16, State 1, Procedure MainProc, Line 19
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

I tried a couple of solutions for this misleading error message, including SET XACT_ABORT ON , however nothing worked! Finally removing the ERROR_MESSAGE() solved it. If anyone knows a solution to this problem, please post it in the comments section.

If you want to see a simpler example of using a Try..Catch block to commit and rollback transaction, check my article Rollback Transaction in Stored Procedures in SQL Server

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook