Declare Variable

  • Jeff.MSSqlSage

    Say Hey Kid

    Points: 709

    mtassin (9/16/2013)


    Good... so rather than googling myself to death trying to come up with the correct term.

    I believe it's because of the IF/THEN that we see this.

    SQL Processes both branches of the IF/THEN statement during parsing/etc and then during execution it determines which plan to use. I'm totally guessing here, but it would appear that variable declarations are considered part of the if/then block not a result of execution of it. So the IF/THEN is actually part of the creation of the variable, and such, when the IF/THEN finishes we have the variable.

    It would explain why this generates an error, essentially the DECLARE statements are part of the IF/THEN block and the declares become available after it completes.

    I think this is the best explanation available.

    A similar issue was listed here[/url] (explanation is wrong, but corrected in the comments).

    It's not exactly the same behavior, but there's an anecdotal comment about "block scope" that applies to this situation. When the parser enters the IF/THEN (Or Loop construct), the body is only created once. It looks like the IF/THEN construct is treated the same way, evaluating all available paths first - including variable declarations.

    Or, to point-grub, "Nice easy back-to-basics question".

  • TomThomson

    SSC Guru

    Points: 104773

    Jeff.MSSqlSage (9/16/2013)


    mtassin (9/16/2013)


    Good... so rather than googling myself to death trying to come up with the correct term.

    I believe it's because of the IF/THEN that we see this.

    SQL Processes both branches of the IF/THEN statement during parsing/etc and then during execution it determines which plan to use. I'm totally guessing here, but it would appear that variable declarations are considered part of the if/then block not a result of execution of it. So the IF/THEN is actually part of the creation of the variable, and such, when the IF/THEN finishes we have the variable.

    It would explain why this generates an error, essentially the DECLARE statements are part of the IF/THEN block and the declares become available after it completes.

    I think this is the best explanation available.

    Pity it's incorrect, then.

    If it were correct, both batches in the following code would produce an error, since in each the variable is used before the IF/THEN which contains the declaration finishes.

    if 0 = 1

    begin

    declare @j-2 int

    end

    else

    begin

    set @j-2 = 3

    end

    select @j-2;

    go

    if 1=1

    begin

    declare @j-2 int

    end

    else set @j-2 = 2;

    select @j-2;

    The first batch selects 3, and the second batch selects NULL. No errors.

    Also, the assignment part of a declaration is executed at run time, not at parse time, so it is executed in an if compound only if the declaration is in the TRUE branch, as you can see from

    if 0=1

    begin

    declare @j-2 int = 3

    end

    select @j-2

    which selects NULL, not 3.

    Tom

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Steve Jones - SSC Editor (9/16/2013)


    If someone has a good explanation that makes sense, I'm happy to change it.

    The batch is processed in two phases.

    Phase one is parsing. This works top to bottom and ignores all control-flow. That's why, if you declare the same variable in a THEN and ELSE, it will raise an error for declaring it twice, even though always exactly one will execute. And that's why, if you declare a variable in a loop that runs multiple time, it will not raise an error. The parser simply reads the text top to bottom, and the only requirement is that references to the variable are "below" the declaration.

    Phase two is execution. This phase respects control-flow, but not variable declaration.

    I don't know if there are any references for this, and I don't have the time to look for them now.

    EDIT: Forgot to add that while this may be interesting to know, bast practice is to put all declarations at the start of a code block, before any executable statements. (Mixing the two in the form of assignment on declaration is okay. Remember that "DECLARE @i int = 0;" is actually just shorthand for "DECLARE @i int; SET @i = 0;" to understand how it behaves).


    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/

  • TomThomson

    SSC Guru

    Points: 104773

    Hugo Kornelis (9/16/2013)


    Steve Jones - SSC Editor (9/16/2013)


    If someone has a good explanation that makes sense, I'm happy to change it.

    The batch is processed in two phases.

    Phase one is parsing. This works top to bottom and ignores all control-flow. That's why, if you declare the same variable in a THEN and ELSE, it will raise an error for declaring it twice, even though always exactly one will execute. And that's why, if you declare a variable in a loop that runs multiple time, it will not raise an error. The parser simply reads the text top to bottom, and the only requirement is that references to the variable are "below" the declaration.

    Phase two is execution. This phase respects control-flow, but not variable declaration.

    I don't know if there are any references for this, and I don't have the time to look for them now.

    EDIT: Forgot to add that while this may be interesting to know, bast practice is to put all declarations at the start of a code block, before any executable statements. (Mixing the two in the form of assignment on declaration is okay. Remember that "DECLARE @i int = 0;" is actually just shorthand for "DECLARE @i int; SET @i = 0;" to understand how it behaves).

    That's technically identical to the explanation in my first comment above, but is a much better way of saying it. Steve, how about using this text of Hugo's to replace the explanation (except for the incorrect bit about initialisation that he tacked on as an afterthought; the variable being declared is not in scope in the initialisation expression, but would be if the initialisation was just the shorthand Hugo says it is; declare @x int; set @x = isnull(@x,3) is valid t-sql, but declare @x int = isnull(@x,3) is not.

    Tom

  • Revenant

    SSC-Forever

    Points: 42467

    Hugo and Tom are the best explainers, as usual.

  • Hany Helmy

    SSChampion

    Points: 13488

    Revenant (9/16/2013)


    Hugo and Tom are the best explainers, as usual.

    +1

  • MaBear

    SSC Veteran

    Points: 295

    L' Eomot Inversé (9/16/2013)


    Koen Verbeeck (9/15/2013)


    The question itself is great, however the explanation is lacking.

    There's no reference, and the explanation itself is incorrect.

    There's no variable that is "reset".

    The DECLARE is not ignored, it is interpreted by the parser/optimizer/interpreter/"whatever relevant part of the process" as if was at the start of the batch, instead of inside a IF-ELSE construct.

    It is a good question and the explanation is indeed incorect. However, so is your correction: if the declaration were treated as if it were at the start of the batch, its scope would start at the beginning of the batch; but it doesn't; the variable is in scope at every point in the batch's text after the declaration, and at no point in or before the declaration. You can see this by putting "set @j-2=9" anywhere in the batch before the declaration and watching the error, which demonstrates that the variable is out of scope before the declaration, or - in versions of SQL Server that allow declarations to include initialisation - make the declaration read "declare @j-2 int = coalesce(@j,9)" which generates the same error, demonstrating that the variables scope begins after the delare statement, not inside it.

    Thanks for the explanation!

  • sanjay.barik

    SSC Rookie

    Points: 49

    I didn't get the accurate reason of this query snippet.

  • marlon.seton

    SSCrazy

    Points: 2623

    Hany Helmy (9/15/2013)


    Nice & easy.

    It was only easy for me because there was a question covering the same concept a little while ago.

  • SQLRNNR

    SSC Guru

    Points: 281243

    Koen Verbeeck (9/15/2013)


    The question itself is great, however the explanation is lacking.

    There's no reference, and the explanation itself is incorrect.

    There's no variable that is "reset".

    The DECLARE is not ignored, it is interpreted by the parser/optimizer/interpreter/"whatever relevant part of the process" as if was at the start of the batch, instead of inside a IF-ELSE construct.

    +1

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • jfgoude

    SSCrazy

    Points: 2586

    seemed to understand it well BEFORE all these explanations !

    one thing is sure begin always by declaring variables, never play with declare in flow of code,

    keep simple : do simple

    +1

Viewing 11 posts - 31 through 41 (of 41 total)

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