@@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:

When the batch starts, the value of this counter is 0.
Each BEGIN TRANSACTION statement increments this counter.
Each COMMIT TRANSACTION decrements it.
ROLLBACK TRANSACTION sets this counter to 0.

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:

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.

Guide

Reference