CODEWITHSUNDEEP
sql-serverstored-proceduressql-server-2008-r2
Ian Boyd
Ian Boyd

Reputation: 256581

How to rollback a transaction in a stored procedure?

Looking at the SQL Server Books Online, Microsoft seems to have an (incorrect) method of handling nested transactions in a stored procedure:

Nesting Transactions

Explicit transactions can be nested. This is primarily intended to support transactions in stored procedures that can be called either from a process already in a transaction or from processes that have no active transaction.

The example goes on to show a stored procedure that starts its own transaction ("The procedure enforces its transaction regardless of the transaction mode of any process that executes it."):

CREATE PROCEDURE TransProc @PriKey INT, @CharCol CHAR(3) AS
   BEGIN TRANSACTION InProc
      ...
   COMMIT TRANSACTION InProc;

This procedure can then either be called without a transaction running:

EXECUTE TransProc 3,'bbb';

Or with an explicit transaction:

BEGIN TRANSACTION OutOfProc;

EXEC TransProc 1, 'aaa';

COMMIT TRANSACTION OutOfProc

What they don't address is what happens when the stored produre:

There is no:

anywhere in the canonical example.

If i didn't know any better, i would have thought that the line:

The following example shows the intended use of nested transactions.

should actually read

The following example shows the how not to use nested transactions.

Unless someone can make heads or tails of this BOL example?

Upvotes: 17

Views: 98878

Answers (2)

owolabi opeyemi jeleel
owolabi opeyemi jeleel

Reputation: 221

CREATE PROCEDURE [usp_my_procedure_name]
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @trancount int;
  SET @trancount = @@trancount;
  BEGIN TRY
    IF @trancount = 0
      BEGIN TRANSACTION
      ELSE
        SAVE TRANSACTION usp_my_procedure_name;

    -- Do the actual work here

    lbexit:
      IF @trancount = 0
      COMMIT;
  END TRY
  BEGIN CATCH
    DECLARE @error int,
            @message varchar(4000),
            @xstate int;

    SELECT
      @error = ERROR_NUMBER(),
      @message = ERROR_MESSAGE(),
      @xstate = XACT_STATE();

    IF @xstate = -1
      ROLLBACK;
    IF @xstate = 1 AND @trancount = 0
      ROLLBACK
    IF @xstate = 1 AND @trancount > 0
      ROLLBACK TRANSACTION usp_my_procedure_name;

    RAISERROR ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message);
  END CATCH
END

Upvotes: 20

Mayur Desai
Mayur Desai

Reputation: 713

You need to use the try catch block with the transaction. So in case you get the error in your catch block then you can rollback your transaction.

Please see the below sql server code for that.

BEGIN TRANSACTION;

BEGIN TRY
    -- Some code
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH

    ROLLBACK TRANSACTION;
END CATCH;

Upvotes: 49

Related Questions