Roll back in SQL Server 2005

Question

My question: Is it possible to rollback a stored procedure from another stored procedure in SQL Server 2005?

I have SP1 to insert the values into one table and SP2 to insert the values into another table. So, if any error comes while executing the SP2 I want to rollback the SP1 also.

Please anyone help me to solve my problem.

Thanks, Bharath

Answer 1

You need to wrap both calls in a single transaction.

if you call them in SQL then this is way, or using a more comprehensive version like the other answer by answered 7 mins ago gbn.

create proc doall as
BEGIN TRY
     BEGIN TRAN
     EXECUTE SP1
     EXECUTE SP1
     COMMIT TRAN
END TRY

BEGIN CATCH
     ROLLBACK TRAN
END CATCH;

if you're calling the SPs from another source, such as from a non SQL program, you need to setup the outer transaction using a Microsoft Distributed Transaction Coordinator (MSDTC) service.

Depending on the API you're using you set up the transaction in Code, and then commit and rollback in code, dependant on conditions.

for example in .net you can use the System.Transactions namespace to create distributed transactions.

In the main program

var tran = new System.Transactions.Transaction();

.
.
.

in one piece of code doe a db call (and pass the tran object to the sql connection) so it enlists in the transaction... if it fails - abort the transaction (trans.Rollback())

.
.
.
. 

in another piece of code do another db call (and pass the tran object to the sql connection) so it enlists in the transaction... if it fails - abort the transaction (trans.Rollback())
.
.
.
later...
if both pieces of code succeed commit the transaction 

This is a good introduction to the this namespace if you're using .net

Answer 2

You need a wrapper stored procedure to manage the transaction.

@@TRANCOUNT on entry and exit to a stored procedure must be the same, otherwise you get error 266. So you can't exit SP1 having started a TXN for example.

I assume that SP1 and SP2 ca nbe called standalone so you need a nested transaction. Then you hit the same error because

• BEGIN TRAN adds one to @@TRANCOUNT
• COMMIT TRAN subtracts one from @@TRANCOUNT
• ROLLBACK makes @@TRANCOUNT zero

So you can still get error 266.

My answer here explains more about it, including nesting, error 266 suppression etc: Nested stored procedures containing TRY CATCH ROLLBACK pattern?

So in your case, you need soemthing like this

CREATE PROCEDURE Wrapper
AS
SET XACT_ABORT, NOCOUNT ON

DECLARE @starttrancount int

BEGIN TRY
    SELECT @starttrancount = @@TRANCOUNT

    IF @starttrancount = 0
        BEGIN TRANSACTION

    EXEC SP1
    EXEC SP2

    IF @starttrancount = 0 
        COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION
    RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
END CATCH
GO