@@TRANCOUNT (TSQL Function)

Returns the current nesting level of transaction.

Syntax

@@TRANCOUNT

Return Types

Returns int.

Remarks

When no explicit transaction is active, @@TRANCOUNT returns 0.

The @@TRANCOUNT counter is modified as follows:

Examples (nested transaction)

PRINT @@TRANCOUNT;  -- @@trancount == 0

BEGIN TRAN;
PRINT @@TRANCOUNT;  -- @@trancount == 1

BEGIN TRAN;
PRINT @@TRANCOUNT;  -- @@trancount == 2

COMMIT TRAN;        -- this commit only decrements the counter
PRINT @@TRANCOUNT;  -- @@trancount == 1

COMMIT TRAN;        -- this commit make all changes permanent, and decrements the counter
PRINT @@TRANCOUNT;  -- @@trancount == 0

The result is:

0
1
2
1
0

Examples (nested transaction with rollback)

PRINT @@TRANCOUNT;  -- @@trancount == 0

BEGIN TRAN;
PRINT @@TRANCOUNT;  -- @@trancount == 1

BEGIN TRAN;
PRINT @@TRANCOUNT;  -- @@trancount == 2

ROLLBACK TRAN;      -- rollback sets the counter to 0
PRINT @@TRANCOUNT;  -- @@trancount == 0

COMMIT TRAN;        -- an error is raised
PRINT @@TRANCOUNT;

The result is:

0
1
2
0
2016/03/27 19:40:36 23:5 [ERROR_GENERAL/ERROR_COMMIT_WITHOUT_BEGIN_TRAN/ERROR_BATCH_ABORT/1]
<COMMIT TRANSACTION failed: no corresponding BEGIN TRAN.>

ROLLBACK TRANSACTION discards all the changes made since the outermost BEGIN TRANSACTION statement.

If you use ROLLBACK TRANSACTION, you will probably want to call RETURN just after.