Nested stored procedures containing TRY CATCH ROLLBACK pattern?

This is our template (error logging removed)

This is designed to handle

• Paul Randal’s article “No such thing as a nested transaction in SQL Server”
• Error 266
• Trigger Rollbacks

Explanations:

• all TXN begin and commit/rollbacks must be paired so that @@TRANCOUNT is the same on entry and exit

• mismatches of @@TRANCOUNT cause error 266 because

  • BEGIN TRAN increments @@TRANCOUNT

  • COMMIT decrements @@TRANCOUNT

  • ROLLBACK returns @@TRANCOUNT to zero

• You can not decrement @@TRANCOUNT for the current scope
  This is what you’d think is the “inner transaction”

• SET XACT_ABORT ON suppresses error 266 caused by mismatched @@TRANCOUNT
  And also deals with issues like this “SQL Server Transaction Timeout” on dba.se

• This allows for client side TXNs (like LINQ)
  A single stored procedure may be part of distributed or XA transaction, or simply one initiated in client code (say .net TransactionScope)

Usage:

• Each stored proc must conform to the same template

Summary

• So don’t create more TXNs than you need

The code

CREATE PROCEDURE [Name]
AS
SET XACT_ABORT, NOCOUNT ON

DECLARE @starttrancount int

BEGIN TRY
    SELECT @starttrancount = @@TRANCOUNT

    IF @starttrancount = 0
        BEGIN TRANSACTION

       [...Perform work, call nested procedures...]

    IF @starttrancount = 0 
        COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION;
    THROW;
    --before SQL Server 2012 use 
    --RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc]
END CATCH
GO

Notes:

• The rollback check is actually redundant because of SET XACT_ABORT ON. However, it makes me feel better, looks odd without, and allows for situations where you don’t want it on

• Remus Rusanu has a similar shell that uses save points. I prefer an atomic DB call and don’t use partial updates like their article