Variable Declaration

  • paulcauchon

    SSC Veteran

    Points: 245

    Comments posted to this topic are about the item Variable Declaration

  • palotaiarpad

    SSCertifiable

    Points: 5615

    Good question! Learned something. Parsing does not set initial values! 🙂

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Very similar to the question a few days ago, but a bit harder.

    Really nice question, thanks.

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

  • bclavier

    SSC Enthusiast

    Points: 137

    SQL server 2005

    Msg 139, Level 15, State 1, Line 0

    Cannot assign a default value to a local variable.

    Msg 137, Level 15, State 2, Line 7

    Must declare the scalar variable "@word".

    Msg 137, Level 15, State 2, Line 11

    Must declare the scalar variable "@word".

    Msg 137, Level 15, State 1, Line 12

    Must declare the scalar variable "@word".

    Msg 137, Level 15, State 2, Line 13

    Must declare the scalar variable "@word".

  • paul s-306273

    SSChampion

    Points: 10615

    bclavier (9/19/2013)


    SQL server 2005

    Msg 139, Level 15, State 1, Line 0

    Cannot assign a default value to a local variable.

    Msg 137, Level 15, State 2, Line 7

    Must declare the scalar variable "@word".

    Msg 137, Level 15, State 2, Line 11

    Must declare the scalar variable "@word".

    Msg 137, Level 15, State 1, Line 12

    Must declare the scalar variable "@word".

    Msg 137, Level 15, State 2, Line 13

    Must declare the scalar variable "@word".

    +1

  • Keld Laursen (SEGES)

    SSC Eights!

    Points: 843

    Interesting.

    I failed because I didn't remember the discussion about the QOTD from the other day. There it were made abundantly clear that the declaration and assignment were made in two passes. Thus, the variable exists, but the apple isn't put into it.

    Well, can't win them all...

  • This was removed by the editor as SPAM

  • Keld Laursen (SEGES)

    SSC Eights!

    Points: 843

    Stewart "Arturius" Campbell (9/19/2013)


    good question, thanks.

    bclavier (9/19/2013)


    SQL server 2005

    Msg 139, Level 15, State 1, Line 0

    Cannot assign a default value to a local variable.

    <snip>

    this will be expected pre SQL2008. for those, you cannot assign a value to a variable in line. therefore, the variable will not be declared.

    to make this work in pre SQL2008, make the following change:

    if 1=0

    begin

    declare @word varchar(100)

    select @word = 'apple'

    declare @table table(id int)

    <snip>

    Which will sort of eliminate the "declaration and assignment in same line" pitfall that were exposed in the QOTD.

    In this case, I think it would have been prudent to mention the version restrictions in the question.

  • SQLDoubleG

    Hall of Fame

    Points: 3090

    Thanks for the question.

    Interesting the logical execution of

    declare @word varchar(100) = 'apple'

    in two phases.

  • Toreador

    SSChampion

    Points: 11257

    I'd never have got this right if it weren't for the discussion following the other recent variable declaration question!

    Interesting to know how SQLServer behaves - but hopefully such shoddy code wouldn't get written in real life 😉

  • This was removed by the editor as SPAM

  • SQL Swerver

    Hall of Fame

    Points: 3074

    This one plays with my mind.

    On the one hand it gives a demonstration of how not to write code and explains why by giving an useful insight into the way that SQL Server parses code.

    On the other hand I find the question annoying because it is asking you how SQL Server responds to such bad code, except that I don't write code like that so it's not a failing on my part that I got the answer wrong. Grrrrr!

  • Dscheypie

    SSCommitted

    Points: 1545

    palotaiarpad (9/19/2013)


    Good question! Learned something. Parsing does not set initial values! 🙂

    +1

    ________________________________________________________
    If you set out to do something, something else must be done first.

  • Gary Stacey

    SSC Eights!

    Points: 939

    Stewart "Arturius" Campbell (9/19/2013)


    Keld Laursen (VFL) (9/19/2013)


    I think it would have been prudent to mention the version restrictions in the question.

    if memory serves, QotD only relates to the current mainstream versions of SQL Server at the time of the question being posted. That would, currently, be SQL2008, SQL2008R2 and SQL2012.

    SQL2005 is no longer in mainstream support (despite the number of customers still using it)

    While that's true, I think it would have been useful to mention a version in this case, given the difference in behaviours between versions.

  • Leifton

    SSC Veteran

    Points: 288

    Not sure how everyone is getting this right. The script fails (as i suspected) in SQL 2005, 2K8 and 2K12. Since the first block is not run due to the false condition, how do the variables get declared for the second block?

    Curious

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

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