none
@@TRANCOUNT does NOT return expected value?

    Question

  • Hi all,
    use tempdb;
    go
    
    create table test_input(trade_date varchar(10))
    go
    insert into test_input(trade_date) values('1')
    insert into test_input(trade_date) values('2')
    insert into test_input(trade_date) values('3')
    insert into test_input(trade_date) values('4')
    insert into test_input(trade_date) values('5')
    insert into test_input(trade_date) values('6')
    go
    create table test_output(trade_date varchar(10))
    
    go
    
    select * from test_input
    
    select * from test_output
    
    go
    use tempdb
    go
    
    create proc [p_test]
    as
    
    declare @trade_date char(8)
    declare cur_trade_date insensitive cursor for
    select 
    	distinct trade_date from test_input order by trade_date
    		
    begin
     		
        open cur_trade_date
        fetch next from cur_trade_date into @trade_date
    	while @@FETCH_STATUS=0
    	begin
    	    print 'Outter TranCnt:'+cast(@@trancount as varchar(50))
    	    begin tran
    	    --'1' expected, '1' printed
    	    print 'Inner TranCnt:'+cast(@@trancount as varchar(50))
    		insert into test_output(trade_date)
    		values
    		(
                            --IMPORT:
                            --'1' expected, '2' inserted
                            --What 's Wrong?
    			'TranCNT:'+cast(@@trancount as varchar(50))
    		)
    		if(@trade_date='3' OR @trade_date='5')
    		    begin
    		        rollback
    		    end
    		else
    		    begin
    		        commit
    		    end
    		fetch next from cur_trade_date into @trade_date        		
    	end
    close cur_trade_date
    deallocate cur_trade_date	
    end
    
    GO
    
    USE tempdb
    GO
    
    DECLARE	@return_value int
    
    EXEC	@return_value = [p_test]
    
    SELECT	'Return Value' = @return_value
    
    GO
    select * from test_output

    The print statement issued output as expected.
    the inserted value of @@trancount is '2',however,
    while '1' is the expected value for @@trancount.


    Thanks,
    Andrew
    Interested in BI related technologies
    Thursday, December 17, 2009 3:04 AM

Answers

  • You are basically generating two transactions, one is the BEGIN TRAN, and one DML, so you have two. The value of @@TRANCOUNT = 2 is issued by the system and it has nothing to do with the initial @@TRANCOUNT which is 1. For example, check out the following code

    BEGIN TRAN
    PRINT @@TRANCOUNT--You will see 1 referring to BEGIN TRAN
    CREATE TABLE #t (id INT)
    INSERT INTO #t
    SELECT @@TRANCOUNT
    
    SELECT * FROM #t--You will see 2 which is the DEFAULT issued by the system
    ROLLBACK TRAN
    PRINT @@TRANCOUNT--0 
    Also, check out this code by Denis Gobo and you will notice that the default for @@TRANCOUNT issued by the system is 2, which, I think(I didn't find any documentation yet), that it assumes you have a BEGIN TRAN you will need to COMMIT or ROLLBACK, and a DML, so you basically have 2 transactions.

    CREATE TABLE #Tran (TranCountDefault int DEFAULT (@@TranCount),TranCountPassedIn int)
    DECLARE @trancount int
    SELECT @trancount = @@TranCount
    INSERT #Tran (TranCountPassedIn) VALUES (@trancount)
    SELECT * FROM #Tran


    So, to fix your issue, you can assign it to a variable and insert it instead.






    Abdallah El-Chal, PMP, ITIL, MCTS
    Thursday, December 17, 2009 4:00 AM
  • Andrew,

    Each statement is a transaction. You were writing out @@trancount inside a INSERT VALUES statement, that is the reason @@trancount was incremented to 2. After that statement, it is back to 1.

    So if you need to record @@trancount, you have to save it into a local variable.

    Same consideration for @@ERROR, @@ROWCOUNT; they have to be saved immediately into a local variable because the next statement may change their values.

    I changed your code by assigning @@trancount to @trancount before the insert. It is working as expected.

    Let us know if helpful.


    use tempdb;
    go
    
    create table test_input(trade_date varchar(10))
    go
    insert into test_input(trade_date) values('1')
    insert into test_input(trade_date) values('2')
    insert into test_input(trade_date) values('3')
    insert into test_input(trade_date) values('4')
    insert into test_input(trade_date) values('5')
    insert into test_input(trade_date) values('6')
    go
    create table test_output(trade_date varchar(10))
    
    go
    
    select * from test_input
    
    select * from test_output
    
    go
    use tempdb
    go
    
    create proc [p_test]
    as
    
    declare @trade_date char(8), @trancount int
    declare cur_trade_date insensitive cursor for
    select 
    	distinct trade_date from test_input order by trade_date
    		
    begin
     		
        open cur_trade_date
        fetch next from cur_trade_date into @trade_date
    	while @@FETCH_STATUS=0
    	begin
    	    print 'Outter TranCnt:'+cast(@@trancount as varchar(50))
    	    begin tran
    	    --'1' expected, '1' printed
    	    print 'Inner TranCnt:'+cast(@@trancount as varchar(50))
    	    set @trancount = @@TRANCOUNT
    		insert into test_output(trade_date)
    		values
    		(
                            --IMPORT:
                            --'1' expected, '2' inserted
                            --What 's Wrong?
    			'TranCNT:'+cast(@trancount as varchar(50))
    		)
    		if(@trade_date='3' OR @trade_date='5')
    		    begin
    		        rollback
    		    end
    		else
    		    begin
    		        commit
    		    end
    		fetch next from cur_trade_date into @trade_date        		
    	end
    close cur_trade_date
    deallocate cur_trade_date	
    end
    
    GO
    
    USE tempdb
    GO
    
    DECLARE	@return_value int
    
    EXEC	@return_value = [p_test]
    
    SELECT	'Return Value' = @return_value
    
    GO
    select * from test_output
    GO
    DROP PROC [p_test]
    DROP TABLE test_input
    DROP TABLE test_output

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Thursday, December 17, 2009 4:05 AM

All replies

  • You are basically generating two transactions, one is the BEGIN TRAN, and one DML, so you have two. The value of @@TRANCOUNT = 2 is issued by the system and it has nothing to do with the initial @@TRANCOUNT which is 1. For example, check out the following code

    BEGIN TRAN
    PRINT @@TRANCOUNT--You will see 1 referring to BEGIN TRAN
    CREATE TABLE #t (id INT)
    INSERT INTO #t
    SELECT @@TRANCOUNT
    
    SELECT * FROM #t--You will see 2 which is the DEFAULT issued by the system
    ROLLBACK TRAN
    PRINT @@TRANCOUNT--0 
    Also, check out this code by Denis Gobo and you will notice that the default for @@TRANCOUNT issued by the system is 2, which, I think(I didn't find any documentation yet), that it assumes you have a BEGIN TRAN you will need to COMMIT or ROLLBACK, and a DML, so you basically have 2 transactions.

    CREATE TABLE #Tran (TranCountDefault int DEFAULT (@@TranCount),TranCountPassedIn int)
    DECLARE @trancount int
    SELECT @trancount = @@TranCount
    INSERT #Tran (TranCountPassedIn) VALUES (@trancount)
    SELECT * FROM #Tran


    So, to fix your issue, you can assign it to a variable and insert it instead.






    Abdallah El-Chal, PMP, ITIL, MCTS
    Thursday, December 17, 2009 4:00 AM
  • Andrew,

    Each statement is a transaction. You were writing out @@trancount inside a INSERT VALUES statement, that is the reason @@trancount was incremented to 2. After that statement, it is back to 1.

    So if you need to record @@trancount, you have to save it into a local variable.

    Same consideration for @@ERROR, @@ROWCOUNT; they have to be saved immediately into a local variable because the next statement may change their values.

    I changed your code by assigning @@trancount to @trancount before the insert. It is working as expected.

    Let us know if helpful.


    use tempdb;
    go
    
    create table test_input(trade_date varchar(10))
    go
    insert into test_input(trade_date) values('1')
    insert into test_input(trade_date) values('2')
    insert into test_input(trade_date) values('3')
    insert into test_input(trade_date) values('4')
    insert into test_input(trade_date) values('5')
    insert into test_input(trade_date) values('6')
    go
    create table test_output(trade_date varchar(10))
    
    go
    
    select * from test_input
    
    select * from test_output
    
    go
    use tempdb
    go
    
    create proc [p_test]
    as
    
    declare @trade_date char(8), @trancount int
    declare cur_trade_date insensitive cursor for
    select 
    	distinct trade_date from test_input order by trade_date
    		
    begin
     		
        open cur_trade_date
        fetch next from cur_trade_date into @trade_date
    	while @@FETCH_STATUS=0
    	begin
    	    print 'Outter TranCnt:'+cast(@@trancount as varchar(50))
    	    begin tran
    	    --'1' expected, '1' printed
    	    print 'Inner TranCnt:'+cast(@@trancount as varchar(50))
    	    set @trancount = @@TRANCOUNT
    		insert into test_output(trade_date)
    		values
    		(
                            --IMPORT:
                            --'1' expected, '2' inserted
                            --What 's Wrong?
    			'TranCNT:'+cast(@trancount as varchar(50))
    		)
    		if(@trade_date='3' OR @trade_date='5')
    		    begin
    		        rollback
    		    end
    		else
    		    begin
    		        commit
    		    end
    		fetch next from cur_trade_date into @trade_date        		
    	end
    close cur_trade_date
    deallocate cur_trade_date	
    end
    
    GO
    
    USE tempdb
    GO
    
    DECLARE	@return_value int
    
    EXEC	@return_value = [p_test]
    
    SELECT	'Return Value' = @return_value
    
    GO
    select * from test_output
    GO
    DROP PROC [p_test]
    DROP TABLE test_input
    DROP TABLE test_output

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Thursday, December 17, 2009 4:05 AM