@@TRANCOUNT (Transact-SQL)

Applies to: 👁 Image
SQL Server 👁 Image
Azure SQL Database 👁 Image
Azure SQL Managed Instance 👁 Image
Azure Synapse Analytics 👁 Image
Analytics Platform System (PDW) 👁 Image
Warehouse in Microsoft Fabric 👁 Image
SQL database in Microsoft Fabric

Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.

👁 Image
Transact-SQL syntax conventions

Syntax

@@TRANCOUNT 

Return Types

integer

Remarks

The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.

Examples

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 

See Also

BEGIN TRANSACTION (Transact-SQL)
COMMIT TRANSACTION (Transact-SQL)
ROLLBACK TRANSACTION (Transact-SQL)
System Functions (Transact-SQL)