Thursday, 24 May 2012

@@transcount in sql server

Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection .
For ex :
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


Ex2 :- >
  Step 1:

create table a(m varchar(max), i int)
go
 
Step2 : 


declare @cnt int
--Explicit transaction
begin tran
set @cnt=@@trancount
insert into a select 'inside', @@trancount
insert into a select 'inside cnt', @cnt
commit tran

set @cnt=@@trancount

--Implicit transcation
insert into a select 'outside', @@trancount
insert into a select 'outside cnt', @cnt
 
 
Step 3 : 
 

select * from a
 
Output :
inside    2
inside cnt             1

outside 2
outside cnt         0


References :   http://blogs.msdn.com/b/jenss/archive/2010/07/10/usage-of-trancount-in-dml-statements.aspx 


http://technet.microsoft.com/en-us/library/ms187967
 




No comments:

Post a Comment