Variable Declaration

  • palotaiarpad (9/19/2013)


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

    +1

  • Toreador (9/19/2013)


    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 😉

    Very true...I hope code like this would end up in real life about as often as this code would 😉 :

    select 'Hello World!'

    Perhaps even less often-- I can't think of a single instance where a code block wrapped in if 1=0 would be a useful design element.

  • SQL 2005 Necromancers (9/19/2013)


    It no worky in SQL2005! Foul!

    +1

    You all are absolutely correct. I should have specified that the code was written and tested in 2008R2, and would not demonstrate the intended behavior in versions before SQL2008, but that would just have taken the fun out of it! Say high to Server 2003 and Windows XP for me!

    The beauty of this is to understand the intricacies of how SQL Server consumes our code, ultimately to get closer to mastering SQL Server.

    PS-- J/K about the necromancers part...I know a lot of times we have absolutely no say in the environments we inherit or wind up coding in. I'm sure if we had our pick, we'd all be coding in SQL2012 on machines with exabyte drives, a billion gigs of ram, and eighty thousand core processors. I know I would 😀

    Happy coding everyone!

    Paul

  • Hi,

    One small question it might be bit odd to ask it.. jst wanted to understand

    As per the question the variables are declared in the "if" Block, logically speaking the execution does not reach the block as the condition fails.. but how come the declaration is done and else statements are executed perfectly.

    Can't i try the same declaration in else and check if the query posted compiles properly(actually it does not).

    Thanks in advance

  • paulcauchon (9/19/2013)


    Perhaps even less often-- I can't think of a single instance where a code block wrapped in if 1=0 would be a useful design element.

    I've used stuff like that to get around the SET FMTONLY tricks that SSIS pulls.

    Imagine a stored proc that takes 5 minutes to execute to return several million rows.

    Create your temp table

    If 1 = 0

    insert 1 row of test data

    else

    run query that puts results in temp table

    then return the contents of the test table.

    It allows you to develop code a lot faster because you don't have to wait 5 minutes every time you close the source for SQL to analyze it



    --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]

  • mtassin (9/20/2013)


    paulcauchon (9/19/2013)


    Perhaps even less often-- I can't think of a single instance where a code block wrapped in if 1=0 would be a useful design element.

    I've used stuff like that to get around the SET FMTONLY tricks that SSIS pulls.

    Imagine a stored proc that takes 5 minutes to execute to return several million rows.

    Create your temp table

    If 1 = 0

    insert 1 row of test data

    else

    run query that puts results in temp table

    then return the contents of the test table.

    It allows you to develop code a lot faster because you don't have to wait 5 minutes every time you close the source for SQL to analyze it

    I have also used similar tricks when dealing with SSIS. It is also helpful with SSRS on occasion.

    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

  • Good question - thanks, Paul!

  • This works in Sql 2008 and above but fails in SQL 2005.

    [font="Times New Roman"]Anil Kubireddi[/font]

  • Koen Verbeeck (9/19/2013)


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

    Really nice question, thanks.

    Ditto

  • I'm running SQL Server 2000.

    This code throws an error like the one for version 2005.

  • Leifton (9/19/2013)


    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

    I tried it in 2008 R2 and got the expected results.

  • If I found a DECLARE nested in an IF like that in real life, there would be some high velocity pork chops in the future of the Develper that wrote it. 🙂

    Excellent question, though!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks to some QOTD asked some time back, I was able to answer it correctly without giving second thought. The errors with the script in SQL 2005 were interesting.

    Though it's bad practice to do it this way, good attempt to clear the point. 🙂

  • Good Question! From now on I should learn to think in 208R2 and newer (at least for QOTD). :hehe:

    Good (if embarrassing) reminder about the parsing vs. execution flow.

  • Luis Cazares (9/19/2013)


    There's not an "official" version. ...

    The question might have needed to specify it should be used on 2088+ but the syntax shows it clearly ....

    I'm too old for waiting on this one version 2088+ sigh will never this sure !

    +1 as it was the same as previous other day

Viewing 15 posts - 31 through 44 (of 44 total)

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