SQL Server/T-SQL Tutorial/Transaction/TRANCOUNT

Effect of exceptions on transactions

6> BEGIN TRANSACTION
7> GO
1> SELECT 1/0 AS DivideByZero
2> GO
Msg 8134, Level 16, State 1, Server J\SQLEXPRESS, Line 1
Divide by zero error encountered.
1> SELECT @@TRANCOUNT AS ActiveTransactionCount
2> GO
ActiveTransactionCount
----------------------
                     0
(1 rows affected)
1>

Returning Number of Open Transactions

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)