So over the last couple of posts I’ve talked about the fact that the ROLLBACK command will roll back an entire transaction no matter how many layers down the ROLLBACK is executed. Well this has an interesting implication with a stored procedure. If a ROLLBACK command is issued inside of a stored procedure then any transactions begun outside of the stored procedure will be rolled back as well and @@TRANCOUNT will be set to 0.

CREATE TABLE tb_TransactionTest (value int)
GO
-- This stored procedure will roll back a transaction if the 
-- @ROLLBACK parameter is a 1.
CREATE PROCEDURE usp_TransactionTest @Value int, @RollBack bit
AS 
BEGIN
	BEGIN TRANSACTION
	INSERT INTO tb_TransactionTest VALUES (@Value)
	IF @Rollback = 1 
		-- If the procedure is called from within a transaction
		-- this is going to cause us to have a different 
		-- @@TRANCOUNT when we exit the procedure than when we
		-- started it.
		ROLLBACK TRANSACTION
	ELSE
		COMMIT
END
GO

-- Begin a new transaction
BEGIN TRANSACTION
INSERT INTO tb_TransactionTest VALUES (1)
-- Run the sp with the param to roll back a transaction
-- This will return an error because tran count has changed
EXEC usp_TransactionTest 2,1
-- Run the commit to close the initial transaction
-- This will return an error because there is no valid transaction
-- to commit.
COMMIT
-- No rows are in the table because the initial insert was
-- rolled back.
SELECT * FROM tb_TransactionTest
GO

The obvious problem here is that any code that uses that stored procedure is going to have to check @@TRANCOUNT before issuing a ROLLBACK or a COMMIT or risk an error because there is no transaction to close. The less obvious problem is that SQL doesn’t like it if the transaction count is different after the execution of a stored procedure. So in the example above we are actually going to get two errors and no data in the tb_TransactionTest table. The solution to both problems is to use the SAVE TRANSACTION command inside the stored procedure.

-- This stored procedure will roll back a saved transaction if 
-- the @ROLLBACK parameter is a 1.
ALTER PROCEDURE usp_TransactionTest @Value int, @RollBack bit
AS 
BEGIN
    BEGIN TRANSACTION
    SAVE TRANSACTION TranTest
    INSERT INTO tb_TransactionTest VALUES (@Value)
    IF @Rollback = 1 
        -- Roll back to the saved point.
        -- The transaction is not closed and  
        -- @@TRANCONT is not changed.
        ROLLBACK TRANSACTION TranTest
    -- Close the transaction created at the beginning of the SP
    COMMIT
END
GO

TRUNCATE TABLE tb_TransactionTest
-- Begin a new transaction
BEGIN TRANSACTION
INSERT INTO tb_TransactionTest VALUES (1)
-- Run the sp with the param to roll back a transaction
EXEC usp_TransactionTest 2,1
-- Run the commit to close the initial transaction
COMMIT
SELECT * FROM tb_TransactionTest
GO

This time at the end of the batch we have no errors and a row with a 1 in tb_TransactionTest. Now this was a very simple example and there is a much better one in BOL under SAVE TRANSACTION that I highly recommend reviewing before dealing with a transaction inside a stored procedure.

Transactions are a big subject which I’m going to explore over several posts. I am by no means going to cover the subject exhaustively but if you have any subjects you would like me to cover or think I’ve missed something feel free to comment or email me.

• Transactions: Rolling back a nested transaction.
• Transactions: Rolling back part of a transaction.
• Transactions: What are they?
• Transactions: Who, What and Where
• Transactions: Creating a single restore point across multiple databases.
• Transactions: What commands aren’t allowed.