Variable Declaration

  • Comments posted to this topic are about the item Variable Declaration

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

  • 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

  • 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".

  • 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

  • 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

  • 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.

  • Thanks for the question.

    Interesting the logical execution of

    declare @word varchar(100) = 'apple'

    in two phases.

  • 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

  • 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!

  • 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.

  • 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.

  • 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