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?

Answer 1

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;