XACT_STATE()

XACT_STATE() 1 -- commitable; 0 -- have no trasaction; -1 -- uncommitable

 

 

IF (XACT_STATE() = 1) AND (@itc = 0) COMMIT TRAN;

1 -- The session has an active transaction. The session can perform any actions, including writing data and committing the transaction.

 

 

0 -- There is no transaction active for the session.

-1 -- The session has an active transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction.

 

SAMPLE 1:

USE AdventureWorks;
GO

-- SET XACT_ABORT ON will render the transaction uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;
        -- A foreign key constraint exists on this table. This 
        -- statement will generate a constraint violation error.
        DELETE FROM Production.Product
            WHERE ProductID = 980;

    -- If the delete succeeds, commit the transaction. The CATCH
    -- block will not execute.
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Test XACT_STATE for 0, 1, or -1.
    -- If 1, the transaction is committable.
    -- If -1, the transaction is uncommittable and should 
    --     be rolled back.
    -- XACT_STATE = 0 means there is no transaction and
    --     a COMMIT or ROLLBACK would generate an error.

    -- Test if the transaction is uncommittable.
    IF (XACT_STATE()) = -1
    BEGIN
        PRINT 'The transaction is in an uncommittable state.' +
              ' Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

    -- Test if the transaction is active and valid.
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT 'The transaction is committable.' + 
              ' Committing transaction.'
        COMMIT TRANSACTION;   
    END;
END CATCH;
GO

SAMPLE 2:

USE [CoreDB]
GO
/****** Object:  StoredProcedure [FlexGoV25].[sp_AddConfig]      Script Date: 01/04/2008 12:38:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [FlexGoV25].[sp_AddConfig]
      @NameSpace  nvarchar(50),
      @Name         nvarchar(50),
      @Value        nvarchar(256),
 @Type         smallint
AS
      DECLARE
          @Return           int,
          @itc              int;                
     
      SELECT
          @Return = 1, 
          @itc = @@TRANCOUNT;
              
      SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

      BEGIN TRY
          --The proc is not called by another transaction,begin transacton
          IF (@itc = 0) BEGIN TRANSACTION;
          
          BEGIN TRY 
              INSERT INTO [FlexGoV25].[Configuration] 
                  (Type, NameSpace, Name, Value)
                  VALUES (@Type, @NameSpace, @Name, @Value);
          END TRY
          BEGIN CATCH

              IF ERROR_NUMBER() = 2601  --unique index violation
              BEGIN
                  --Namespace and Name exist, update the value
                  UPDATE [FlexGoV25].[Configuration] 
                      SET Value = @Value
                      WHERE Type = @Type AND NameSpace = @NameSpace AND Name = @Name;
              END
              ELSE
              BEGIN
                  --Throw retry exception
                  GOTO FAILED;
              END
          END CATCH

          SELECT @Return = 0;
COMMITTRAN:
          -- commit transaction if the proc begin the transaction and active
          IF (XACT_STATE() = 1) AND (@itc = 0) COMMIT TRAN;
          RETURN @Return
      END TRY
      BEGIN CATCH
          GOTO FAILED;
      END CATCH
FAILED:
          -- rollback if the transaction is active and start from the proc
          -- @itc is the the initial transaction count when it enters the proc,
          -- XACT_STATE() is zero when the transaction inactive
          IF (@itc = 0) AND (@@TRANCOUNT > 0) AND (XACT_STATE() <> 0) ROLLBACK TRAN;