Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Transactions: Rolling back a transaction inside a stored procedure.

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.


Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL, Transactions Tagged: code language, language sql, microsoft sql server, T-SQL, transactions

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...