How to rollback a transaction in a stored procedure?

Question

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:

fails with an error, but leaves the transaction running
fails with an error, but doesn't leave the transaction running
encounters an error, but continues executing with the transaction open
encounters an error, but continues executing with the transaction rolled back

There is no:

SET XACT_ABORT ON
@@TRANCOUNT

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?

See http://rusanu.com/2009/06/11/exception-handling-and-nested-transactions/ — Remus Rusanu, Jul 17 '12 at 22:01
Also chk http://stackoverflow.com/questions/9692734/sql-server-nested-transactions-in-a-stored-procedure — vmvadivel, Jul 18 '12 at 03:26
@RemusRusanu Now throw in `SET XACT_ABORT ON` and `BEGIN DISTRIBUTED TRANSACTION` — Ian Boyd, Jul 18 '12 at 13:48

score 46 · Answer 1 · answered Nov 01 '12 at 05:28

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;

score 18 · Answer 2 · edited Aug 25 '16 at 20:12

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

Good explanation of this code [here](http://rusanu.com/2009/06/11/exception-handling-and-nested-transactions/). — , Aug 30 '18 at 15:25

How to rollback a transaction in a stored procedure?

Nesting Transactions

2 Answers2

Linked