Share via

  • Article

@@TRANCOUNT (Transact-SQL)

Returns the number of active transactions for the current connection.

Topic link iconTransact-SQL Syntax Conventions

Syntax

@@TRANCOUNT

Return Types

integer

Remarks

The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.

Examples

A. Showing the effects of the BEGIN and COMMIT statements

The following example shows the effect that nested BEGIN and COMMIT statements have on the @@TRANCOUNT variable.

PRINT @@TRANCOUNT
--  The BEGIN TRAN statement will increment the
--  transaction count by 1.
BEGIN TRAN
    PRINT @@TRANCOUNT
    BEGIN TRAN
        PRINT @@TRANCOUNT
--  The COMMIT statement will decrement the transaction count by 1.
    COMMIT
    PRINT @@TRANCOUNT
COMMIT
PRINT @@TRANCOUNT
--Results
--0
--1
--2
--1
--0

B. Showing the effects of the BEGIN and ROLLBACK statements

The following example shows the effect that nested BEGIN TRAN and ROLLBACK statements have on the @@TRANCOUNT variable.

PRINT @@TRANCOUNT
--  The BEGIN TRAN statement will increment the
--  transaction count by 1.
BEGIN TRAN
    PRINT @@TRANCOUNT
    BEGIN TRAN
        PRINT @@TRANCOUNT
--  The ROLLBACK statement will clear the @@TRANCOUNT variable
--  to 0 because all active transactions will be rolled back.
ROLLBACK
PRINT @@TRANCOUNT
--Results
--0
--1
--2
--0