January 6, 2014 by Kenneth Fisher
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.