sql_nested_transactions

CREATE TABLE [unit_test].[data]
  (
     [id]          [INT] IDENTITY(1, 1) NOT NULL
     , [value]     [SYSNAME]
     , [timestamp] [DATETIME]
     CONSTRAINT [pk.unit_test.data.id] PRIMARY KEY ([id]),
     CONSTRAINT [uq.unit_test.data.value] UNIQUE ([value])
  );

ALTER TABLE [unit_test].[data]
  ADD CONSTRAINT [df.unit_test.data.timestamp] DEFAULT ((CURRENT_TIMESTAMP)) FOR [timestamp]

go 

explanation

CREATE PROCEDURE [unit_test].[inner_method]
  @fail [BIT] = 0
AS
  BEGIN
      SET nocount ON;

      DECLARE @message [NVARCHAR](max);

      --
      ---------------------------------------------------------------------------------------------
      -- See my presentation on transaction isolation levels to understand why I default to
      --    either serializable or, preferably, when the database is enabled for it, snapshot.
      --
      SET TRANSACTION isolation level snapshot;

      --
      ---------------------------------------------------------------------------------------------
      -- Check to see if there is an existing transaction in this context. Only if there is not
      --    do we start a new transaction.
      --
      -- Name the transaction so we can easily find it when we need to rollback or commit.
      --
      IF ( XACT_STATE() ) = 0
        BEGIN TRANSACTION tx_inner_method;

      SET @message = N'in transaction';

      BEGIN try
          IF ( @fail = 0 )
            INSERT INTO [unit_test].[data]
                        ([value])
            VALUES      (CAST(CURRENT_TIMESTAMP AS [SYSNAME])
                         + CAST(RAND() AS [SYSNAME]));
          ELSE IF ( @fail = 1 )
            INSERT INTO [unit_test].[data]
                        ([id]
                         , [value])
            VALUES      (1,@message);
      END try

      BEGIN catch
          --
          -----------------------------------------------------------------------------------------
          -- Only rollback the transaction starting within THIS procedure!
          --
          IF EXISTS (SELECT *
                     FROM   [sys].[dm_tran_active_transactions]
                     WHERE  [name] = N'tx_inner_method')
            ROLLBACK TRANSACTION tx_inner_method;

          SET @message = error_message();

          THROW 51000, @message, 1;
      END catch

      --
      ---------------------------------------------------------------------------------------------
      -- Show that we actually DID insert data in this procedure.
      --
      SELECT *
      FROM   [unit_test].[data];

      --
      ---------------------------------------------------------------------------------------------
      -- Only commit the transaction starting within THIS procedure!
      --
      IF EXISTS (SELECT *
                 FROM   sys.dm_tran_active_transactions
                 WHERE  [name] = N'tx_inner_method')
        COMMIT TRANSACTION tx_inner_method;
  END

go

explanation

CREATE PROCEDURE [unit_test].[outer_method]
  @result [XML] output
AS
  BEGIN
      SET nocount ON;

      DECLARE @message        [NVARCHAR](max)
              ,@identity      [INT]
              ,@test          [XML]
              ,@error_message [XML]
              ,@count         [INT]
              ,@error         [XML]

      --
      ---------------------------------------------------------------------------------------------
      -- See my presentation on transaction isolation levels to understand why I default to
      --    either serializable or, preferably, when the database is enabled for it, snapshot.
      --
      SET TRANSACTION isolation level snapshot;
      --
      ---------------------------------------------------------------------------------------------
      -- Get the current identity value of the target object. One of our objectives is to ensure that
      --    the object is returned to this value before we complete.
      --
      SET @identity=IDENT_CURRENT(N'[unit_test].[data]');
      SET @result = N'<result test_count="0" pass_count="0"/>';

      --
      ---------------------------------------------------------------------------------------------
      -- case 1: Valid call
      --
      BEGIN
          --
          -----------------------------------------------------------------------------------------
          -- Check to see if there is an existing transaction in this context. Only if there is not
          --    do we start a new transaction.
          --
          -- Name the transaction so we can easily find it when we need to rollback or commit.
          --
          IF ( XACT_STATE() ) = 0
            BEGIN TRANSACTION tx_outer_method;

          SET @message = N'in transaction';

          BEGIN try
              TRUNCATE TABLE [unit_test].[data];

              SET @test = N'<test case="1" result="fail" procedure="[unit_test].[inner_method]" name="Valid method call" ><description>Simulated call to inner method with no failure expected.</description></test>';

              EXECUTE [unit_test].[inner_method];

              SET @test.modify(N'replace value of (/test/@result)[1] with ("pass")');
          END try

          BEGIN catch
              IF EXISTS (SELECT *
                         FROM   [sys].[dm_tran_active_transactions]
                         WHERE  [name] = N'tx_outer_method')
                ROLLBACK;

              SET @message = error_message();

              SELECT 1; --THROW 51000, @message, 1;
          END catch

          IF EXISTS (SELECT *
                     FROM   [sys].[dm_tran_active_transactions]
                     WHERE  [name] = N'tx_outer_method')
            ROLLBACK TRANSACTION tx_outer_method
      END

      SET @result.modify(N'insert sql:variable("@test") as last into (/*)[1]');

      --
      ---------------------------------------------------------------------------------------------
      -- RESEED the identity value into the target object
      --
      DBCC CHECKIDENT (N'unit_test.data', RESEED, @identity) WITH no_infomsgs;

      --
      ---------------------------------------------------------------------------------------------
      -- case 2: Validate identity reseed
      --
      BEGIN
          SET @test = N'<test case="2" result="fail" procedure="[unit_test].[inner_method]" name="Validate identity reseed" ><description>Validate that the identity column has been reseeded to its original value.</description>
          <identity original="'
                      + CAST(@identity AS [SYSNAME])
                      + '" current="'
                      + CAST(IDENT_CURRENT(N'[unit_test].[data]') AS [SYSNAME])
                      + '"/>
          </test>';

          IF ( @identity = IDENT_CURRENT(N'[unit_test].[data]') )
            SET @test.modify(N'replace value of (/test/@result)[1] with ("pass")');
      END

      SET @result.modify(N'insert sql:variable("@test") as last into (/*)[1]');
      --
      ---------------------------------------------------------------------------------------------
      -- Total results
      --
      SET @count = @result.value(N'count (/result/test)', 'int');
      SET @result.modify(N'replace value of (/result/@test_count)[1] with sql:variable("@count")');
      SET @count = @result.value(N'count (/result/test[@result="pass"])', 'int');
      SET @result.modify(N'replace value of (/result/@pass_count)[1] with sql:variable("@count")');
  END

go