@@TRANCOUNT (Transact-SQL)
SQL Server 2008
Returns the number of active transactions for the current connection.
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
