locked
Scope of table variables - Error? RRS feed

  • Question

  • Is this an error?

    SELECT @@VERSION
    /*
    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2769.0 (X64)
     Jun 24 2011 13:09:43
     Copyright (c) Microsoft Corporation
     Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
    */

    DECLARE @loop INT = 3
    WHILE @loop > 0
    BEGIN
     DECLARE @test TABLE (id INT) -- new empty(?) table variable declared
     SELECT * FROM @test
     INSERT INTO @test (id) SELECT @loop -- insert one row
     
     SET @loop-=1
    END

    /*
    id

    id
    3

    id
    3
    2
    */

    --
    Henrik Johansen
    Senior Database Developer

    • Moved by Tom Phillips Monday, August 15, 2011 1:50 PM TSQL question (From:SQL Server Database Engine)
    Saturday, August 13, 2011 12:58 PM

Answers

  • Hi Henrik,

    This is what it seems to me:

    DECLARE @loop INT = 3
    WHILE @loop > 0
    BEGIN
     --DECLARE @test TABLE (id INT) -- new empty(?) table variable declared
     declare @i int
     set @i=@loop
     select @i
     --select id as 'after insert' from @test
    SET @i-=1
    set @loop=@i
    END
     
    *******
    OUTPUT
    *******
    
    
    -----------
    3
    
    (1 row(s) affected)
    
    
    -----------
    2
    
    (1 row(s) affected)
    
    
    -----------
    1
    
    (1 row(s) affected)
    
    
    

    In above output, we declared a variable @i inside a while loop and it gets initialized on first run, on second run it uses the same previously declared variable @i because as per BOOKS ON LINE:

    http://msdn.microsoft.com/en-us/library/ms187953.aspx

    The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared


     So I am expecting the same is applicable here as well.


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++
    Saturday, August 13, 2011 3:28 PM

All replies

  • Hi Henrik,

    This is fine as per my view. I tried code with a minor change i.e. I simply selected values once more after insertion into table and this time it shows all 3 values as expected.

    SELECT @@VERSION
    
    
    
    
    
    DECLARE @loop INT = 3
    
    WHILE @loop > 0
    
    BEGIN
    
     DECLARE @test TABLE (id INT) -- new empty(?) table variable declared
    
     SELECT id as 'before insert' FROM @test 
    
     INSERT INTO @test (id) SELECT @loop -- insert one row
    
     select id as 'after insert' from @test
    
    SET @loop-=1
    
    END
    
     
    
    
    
    
    
    
    
    

    Output that I am getting is:

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    Microsoft SQL Server 2008 (SP1) - 10.0.2573.0 (Intel X86) 
    
    	Feb 4 2011 11:51:02 
    
    	Copyright (c) 1988-2008 Microsoft Corporation
    
    	Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
    
    
    
    
    
    (1 row(s) affected)
    
    
    
    before insert
    
    -------------
    
    
    
    (0 row(s) affected)
    
    
    
    (1 row(s) affected)
    
    
    
    after insert
    
    ------------
    
    3
    
    
    
    (1 row(s) affected)
    
    
    
    before insert
    
    -------------
    
    3
    
    
    
    (1 row(s) affected)
    
    
    
    (1 row(s) affected)
    
    
    
    after insert
    
    ------------
    
    3
    
    2
    
    
    
    (2 row(s) affected)
    
    
    
    before insert
    
    -------------
    
    3
    
    2
    
    
    
    (2 row(s) affected)
    
    
    
    (1 row(s) affected)
    
    
    
    after insert
    
    ------------
    
    3
    
    2
    
    1
    
    
    
    (3 row(s) affected)
    
    
    
    
    
    


    Now why. this is because when the last value got inserted i.e. 1 after that value of loop will be 0 so it will not go into the WHILE loop: check in this way

     

    @Loop = 3
    
    
    
    While 3>0  -- First While Loop
    
    NULL	 -- First SELECT
    
    insert 3 	 -- First INSERT with value 3
    
    3-1=2	 -- First Substract
    
    
    
    @Loop = 2
    
    2>0	 -- Second While Loop
    
    3	 -- Second SELECT (Value is 3 from previous Insert)
    
    insert 2	 -- Second INSERT with value 2
    
    2-1=1	 -- Second Substract
    
    
    
    @Loop = 1
    
    1>0	 -- Third While Loop
    
    3	 -- Third SELECT (value is 3 and 2 from previous 2 
    
    2      inserts)
    
    insert 1	 -- Third INSERT with value 1
    
    1-1=0	 -- Third Subsctract
    
    
    
    After this value of @Loop is 0 so it will not go to WHILE loop
    
    so will not display records. If we want to see records then we
    
    have do a SELECT either after INSERT or After number Substraction (as per above code which I wrote).
    
    
    
    

     


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++
    • Edited by GURSETHI Saturday, August 13, 2011 2:48 PM modified
    Saturday, August 13, 2011 2:37 PM
  • This is quite obvious, and is not my problem at all.

     

    The question is why the :

    DECLARE @test TABLE (id INT) -- new empty(?) table variable declared

    SELECT id as [before insert] FROM @test

    ... does NOT return an empty resultset in loop 2 and 3.

    I create a new table variable, and would indeed expect it to be empty, because the "same" variable from previous loop,

    should be out of scope. If the variable from previous loop was not out of scope I would expect an "Variable has already been declared"-error.

    So how can the select return anything BEFORE inserting into the table-variable?

    --

    Henrik Johansen

    Senior Database Developer

    Saturday, August 13, 2011 2:51 PM
  • Hi Henrik,

    That is what I was surprised, any how in original post question was not clear. Thx for clearing it out.


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++
    Saturday, August 13, 2011 3:08 PM
  • Hi Henrik,

    This is what it seems to me:

    DECLARE @loop INT = 3
    WHILE @loop > 0
    BEGIN
     --DECLARE @test TABLE (id INT) -- new empty(?) table variable declared
     declare @i int
     set @i=@loop
     select @i
     --select id as 'after insert' from @test
    SET @i-=1
    set @loop=@i
    END
     
    *******
    OUTPUT
    *******
    
    
    -----------
    3
    
    (1 row(s) affected)
    
    
    -----------
    2
    
    (1 row(s) affected)
    
    
    -----------
    1
    
    (1 row(s) affected)
    
    
    

    In above output, we declared a variable @i inside a while loop and it gets initialized on first run, on second run it uses the same previously declared variable @i because as per BOOKS ON LINE:

    http://msdn.microsoft.com/en-us/library/ms187953.aspx

    The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared


     So I am expecting the same is applicable here as well.


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++
    Saturday, August 13, 2011 3:28 PM
  • Wow. So each iteration in the while-loop does not make the inner variable to go out of scope?

    If that is the case I would have expected a "Variable has already been declared"-error the second time I tried to declare the variable.

     

    DECLARE @loop INT = 2
    WHILE @loop > 0
    BEGIN
     DECLARE @i INT
     
     SELECT @i [before] -- Would expect this was always NULL
     SET @i = @loop
     SELECT @i [after]
     
     SET @loop-=1
    END

    before
    NULL

    after
    2

    before
    2

    after
    1

    It seems that declaring a variable inside a loop 'inherits' the value from the previous iteration. This is strange behaviour, if you ask me.

     

    DECLARE @loop INT = 2
    WHILE @loop > 0
    BEGIN
     DECLARE @i INT = 0 -- initializing the variable at declaration time fixes the problem
     
     SELECT @i [before]
     SET @i = @loop
     SELECT @i [after]
     
     SET @loop-=1
    END
    

    I often use table variables inside loops as in my first entry, but it annoys me that I have to [remember to] empty the table before actually using it.
    DECLARE @loop INT = 2
    WHILE @loop > 0
    BEGIN
     DECLARE @test TABLE (id INT) 
     DELETE FROM @test -- seems to be necessary inside a loop :-(
     
     INSERT INTO @test (id) VALUES (1) -- insert one row
     
     SET @loop-=1
    END
    


     


    -- Henrik Johansen Senior Database Developer
    Saturday, August 13, 2011 3:49 PM
  • Well Henrik that is what SQL Books On Line says and examples demostrate above. In your last example you yourself provides the solution for your problem i.e. delete the table variable at the end of while loop and then recreate at the begining, in that way you will achieve what you want.

    So what I can say these are 2 sides of the coin... ;)


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++
    Saturday, August 13, 2011 4:02 PM
  • Well, I dont entirely agree ;-)

     

    DECLARE @i INT
    DECLARE @i INT
    

     

    This would return an error because @i is obviously already declared.

     

    DECLARE @i INT
    GO
    DECLARE @i INT
    

     

    This would work, because 'GO' creates a new batch, making the first @i get out of scope.

     

    I would expect that declaring an variable INSIDE a loop, would either

    a) return an error on second run, because variable was already declared in first iteration. This does not happen, so the variable MUST be out of scope.

    or

    b) declare a new non-initialized instance of the variable

    As stated in a) the variable declared inside the loop, is out of scope in each iteration of the loop. Therefore I would have expected the variable to be uninitialized in second iteration.

    Exactly the same would be expected here:

    DECLARE @i INT = 1
    GO
    DECLARE @i INT 
    SELECT @i
    

     

    In this case I would expect NULL (and fortunately THAT part works as expected)

     

    So back to where I started.

     

    DECLARE @loop INT = 2
    WHILE @loop > 0
    BEGIN
     DECLARE @i INT
     
     SELECT @i [before]
     SET @i = @loop
     SELECT @i [after]
     
     SET @loop-=1
    END
    

     

    Here I would still expect NULL in both [before] rows and I am convinced that it is an error that it doesnt.

    Anyway, Thank you for you answer and the BOL-link!


    -- Henrik Johansen Senior Database Developer
    Saturday, August 13, 2011 4:28 PM
  • Yup, TSQL is not block-structured at all, it just sort of looks that way.

    This works, no error:

    print 'hello world';
    
    begin
    declare @foo int=123;
    end
    
    print @foo;
    
    /*
    hello world
    123
    */
    
    

     

    Josh

     

    Saturday, August 13, 2011 6:04 PM
  • .. And that is why I wonder why it is possible to declare the same variable over and over again:

    DECLARE @loop INT = 10
    WHILE @loop > 0
    BEGIN
    	DECLARE @i INT -- No error - same variable declared 10 times
    	SET @loop = @loop -1
    END
    GO -- New batch
    
    BEGIN
    	DECLARE @i INT -- No error
    END
    BEGIN
    	DECLARE @i INT -- Error
    END
    


    Saturday, August 13, 2011 6:11 PM
  • I think we called something like this "lexical scope" once upon a time in academia.

    Sounds better than "crufty". :)

    Josh

     

    Saturday, August 13, 2011 7:13 PM
  • Wow. So each iteration in the while-loop does not make the inner variable to go out of scope?

    If that is the case I would have expected a "Variable has already been declared"-error the second time I tried to declare the variable.


    No, DECLARE of a local variable is a compile-time statement, at least as long as there is no initiator. At run-time, the statement has no meaning.

    In some very old-fashioned languages like Fortran-77 you can only declare variables in the beginning of a procedure. In modern languages, you can declare variables about anywhere, and when you exit the block they was declared in, they go out of scope.

    T-SQL looks a little like a modern language, but it is more in like with Fortran-77: once declared, a variable is visible until the end of the batch. Yes, this is ugly.

    If there is an initiator, the initiator is executed every time, but the declaration still occurs at compile-time:

    declare @i int = 3
    while @i > 0
    begin
       declare @j int = 90 + @i
       select @i, @j
       select @i -= 1
    end
    select @j

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, August 15, 2011 8:13 PM
  • Wow. So each iteration in the while-loop does not make the inner variable to go out of scope?

    It is always fun to see how some people react when SQL or T-SQL does not react the way they would expect from a language like C#

    With SQL we have a language that you cannot program just by trial and error until the compiler says there are no more errors or warnings. You actually have to know the basics of how SQL works. The SQL parser can simply not give the same level of comfort as what programmers of C# might be used to.

    I think partially, Microsoft is to blame for this as well. It more or less asks for the trial and error approach and blame attitude when the parser doesn't prevent mistakes, because it is adding more and more features that suggest that T-SQL is just another .NET language when it is in fact nothing of the kind.

     


    Gert-Jan
    Monday, August 15, 2011 8:37 PM