table variable declaration inside a loop

  • Sreepathi1987

    Default port

    Points: 1425

    Comments posted to this topic are about the item table variable declaration inside a loop

  • happycat59

    One Orange Chip

    Points: 29195

    A little more info on declaring variables (scalar and table variables) - whilst the example in this question is quite right and the loop does not attempt to create another copy of the table variable, you must declare variables before you attempt to use them

    If the code is adjusted so to be as follows...

    set @I= 1

    DECLARE @i INT

    WHILE @i < 5

    BEGIN

    SET @i = @i + 1

    INSERT INTO @j-2

    SELECT @i

    END

    DECLARE @j-2 AS TABLE

    (

    i INT

    )

    SELECT * FROM @j-2

    you end up with 2 errors because there is an attempt to assign a value to @i before it is declared and an attempt to insert into @j-2 before it is declared.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Mighty

    SSCrazy Eights

    Points: 8820

    For me the way how SQL Server handles the declaration of the variables is a bit strange.

    This piece of code doesn't return an error, even though the declaration of @j-2 doesn't seem to happen.

    DECLARE @i INT = 1

    WHILE @i < 0 -- While loop will not be entered

    BEGIN

    PRINT 'DECLARE @j-2'

    DECLARE @j-2 AS TABLE

    (

    i INT

    )

    SET @i = @i + 1

    INSERT INTO @j-2

    SELECT @i

    END

    SELECT * FROM @j-2

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • Victor Kirkpatrick

    Hall of Fame

    Points: 3672

    Always nice to learn something new - good question. And Mighty's example code a few posts ago is just as interesting.

  • Ed Wagner

    SSC Guru

    Points: 286983

  • Mighty

    SSCrazy Eights

    Points: 8820

    Stewart "Arturius" Campbell (7/30/2014)


    All variables are declared at compile time. at this time, all control flow commands are ignored.

    That is more or less what I was thinking, but in that case it should not matter when you are declaring a variable and when you are actually using it.

  • SqlMel

    SSCrazy

    Points: 2891

    Hmmm... Learned something today.

    Good question. Thanks!

    ---------------
    Mel. 😎

  • Brian.Klinect

    Mr or Mrs. 500

    Points: 592

    If you're allowed to re-declare @j-2 inside the while loop, why are you allowed to select from @j-2 outside of the while loop?

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Mighty (7/30/2014)


    Stewart "Arturius" Campbell (7/30/2014)


    All variables are declared at compile time. at this time, all control flow commands are ignored.

    That is more or less what I was thinking, but in that case it should not matter when you are declaring a variable and when you are actually using it.

    It does matter because the compiler uses a single pass over the batch text. It starts at the top, and proceeds downwards towards the bottom, keeping track of variable declarations it encounters. When a variable is referenced, the compiler can only know what to do with it if it has already registered the declaration. Which means that all declarations must be "before" the first use when scanning the text top to bottom and left to right. Regardsless of control flow statements that might change the order in which statements are executed.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Brian.Klinect (7/30/2014)


    If you're allowed to re-declare @j-2 inside the while loop, why are you allowed to select from @j-2 outside of the while loop?

    See my post above (which I started writing before your post was made). The declare is only used once, durinig the compile phase. It is then ignored when the loop executes, so there is no "re-declaration".


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Thomas Abraham

    SSChampion

    Points: 10761

    Hugo Kornelis (7/30/2014)


    Brian.Klinect (7/30/2014)


    If you're allowed to re-declare @j-2 inside the while loop, why are you allowed to select from @j-2 outside of the while loop?

    See my post above (which I started writing before your post was made). The declare is only used once, durinig the compile phase. It is then ignored when the loop executes, so there is no "re-declaration".

    Maybe Hugo could be turned loose to redo the BOL's so they make sense? 😉

    Thanks to OP for question.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • x

    SSC-Insane

    Points: 23584

    Mighty (7/30/2014)


    Stewart "Arturius" Campbell (7/30/2014)


    All variables are declared at compile time. at this time, all control flow commands are ignored.

    That is more or less what I was thinking, but in that case it should not matter when you are declaring a variable and when you are actually using it.

    Good point! I'm guessing that the tasks of declaring variables and checking that variables are declared are done in the same pass. ('Pass' is what each scan of programming code or t-sql in this case is called, although sometimes the task of scanning an intermediate or target format of the program might be called a 'pass' [citation needed LOL].) So if those two tasks are in a single pass, and a pass happens from top to bottom, even ignoring control, then declarations need to happen before references.

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply