Declare Variable

  • Richard Warr

    SSCertifiable

    Points: 6957

    This isn't a "nice and simple" or "easy one" at all. Although the explanation is wrong the question illustrates a very subtle nuance with the way scripts are run. Logically we'd expect to see an error because the second DECLARE looks like it's never executed. The reason why we don't is not at all obvious.

    _____________________________________________________________________
    MCSA SQL Server 2012

  • erwin oosterhoorn

    SSCrazy

    Points: 2127

    Richard Warr (9/16/2013)


    This isn't a "nice and simple" or "easy one" at all. Although the explanation is wrong the question illustrates a very subtle nuance with the way scripts are run. Logically we'd expect to see an error because the second DECLARE looks like it's never executed. The reason why we don't is not at all obvious.

    Exactly my view, might be simple if you are aware of this otherwise it's not at all.

  • Neeraj Prasad Sharma

    Ten Centuries

    Points: 1285

    Thanx for the question, its good to refresh memory.:-)

    Neeraj Prasad Sharma
    Sql Server Tutorials

  • Ed Wagner

    SSC Guru

    Points: 286982

    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.

    I agree with Koen - good question but the answer is lacking in detail.

  • logitestus

    SSCrazy

    Points: 2877

    Something to wake me up (outside the morning cup of joe).

    Thanks!

  • kamineni_srinivasarao

    Valued Member

    Points: 71

    Hi,

    Just wondered..

    declare @j statement is inside the if block.

    in this scenario sql server have display the error message stating not declared. But how it is ignoring.

    Please explain..

  • Tee Time

    Hall of Fame

    Points: 3693

    Thanks!

  • This was removed by the editor as SPAM

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Richard Warr (9/16/2013)


    This isn't a "nice and simple" or "easy one" at all. Although the explanation is wrong the question illustrates a very subtle nuance with the way scripts are run. Logically we'd expect to see an error because the second DECLARE looks like it's never executed. The reason why we don't is not at all obvious.

    It's an easy question because if my memory serves me right, this question has already been asked before. 😀

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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719720

    There's not reference as the author didn't provide one and I couldn't find one. It's a bit of a hole in the SQL documentation and the way the language works.

    If someone has a good explanation that makes sense, I'm happy to change it. As I read it, the "then" portion is never executed, so the variable is not reset. IIRC, the DECLARE essentially resets the value to null each time without an error.

  • mtassin

    SSC-Insane

    Points: 23099

    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.

    Thanks for the proper explanation Koen 🙂



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • kevin.l.williams

    SSCarpal Tunnel

    Points: 4916

    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.

    http://technet.microsoft.com/en-us/library/ms187953(v=sql.105).aspx

  • TomThomson

    SSC Guru

    Points: 104773

    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=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 int = coalesce(@j,9)" which generates the same error, demonstrating that the variables scope begins after the delare statement, not inside it.

    Tom

  • mtassin

    SSC-Insane

    Points: 23099

    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.

    DECLARE @i INT = 1

    IF @i = 2

    DECLARE @j INT = 3

    ELSE

    DECLARE @j FLOAT = 3.1415

    Msg 134, Level 15, State 1, Line 7

    The variable name '@j' has already been declared. Variable names must be unique within a query batch or stored procedure.

    This seems to happen because until the runtime engine gets to the IF comparison, both paths are considered valid, and when it finishes those resources become available?

    In reality this is a big shrug for me, it's one of those things I've seen happen for years... and it's confused developers and just caused me to just ask them to declare all the variables they need at the beginning of their stored procs.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719720

    kevin.l.williams (9/16/2013)


    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.

    http://technet.microsoft.com/en-us/library/ms187953(v=sql.105).aspx

    Not sure this is a good explanation. The "then" clause is in scope with the variable, but doesn't cause a parser error. I suspect this would be an executable error, but not sure.

Viewing 15 posts - 16 through 30 (of 41 total)

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