Returning Number of Open Transactions : TRANCOUNT « Transaction « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Transaction
3. TRANCOUNT

4>
5> BEGIN TRAN t1
6> SELECT @@TRANCOUNT -- Returns 1
7> BEGIN TRAN t2
8> SELECT @@TRANCOUNT -- Returns 2
9> BEGIN TRAN t3
10> SELECT @@TRANCOUNT -- Returns 3
11> COMMIT TRAN
12> SELECT @@TRANCOUNT -- Returns 2
13> ROLLBACK TRAN
14> SELECT @@TRANCOUNT -- After ROLLBACK, always Returns 0!
15> GO

-----------
          1

(1 rows affected)

-----------
          2

(1 rows affected)

-----------
          3

(1 rows affected)

-----------
          2

(1 rows affected)

-----------
          0

(1 rows affected)