September 19, 2013 at 2:04 pm
palotaiarpad (9/19/2013)
Good question! Learned something. Parsing does not set initial values! 🙂
+1
September 19, 2013 at 5:43 pm
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.
September 19, 2013 at 6:10 pm
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
September 19, 2013 at 10:43 pm
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
September 20, 2013 at 7:36 am
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
September 20, 2013 at 10:39 am
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
September 20, 2013 at 11:43 am
Good question - thanks, Paul!
September 24, 2013 at 1:00 pm
This works in Sql 2008 and above but fails in SQL 2005.
[font="Times New Roman"]Anil Kubireddi[/font]
September 25, 2013 at 3:16 am
Koen Verbeeck (9/19/2013)
Very similar to the question a few days ago, but a bit harder.Really nice question, thanks.
Ditto
September 25, 2013 at 6:51 am
I'm running SQL Server 2000.
This code throws an error like the one for version 2005.
September 25, 2013 at 2:12 pm
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.
October 1, 2013 at 8:14 pm
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
Change is inevitable... Change for the better is not.
October 3, 2013 at 12:48 am
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. 🙂
October 4, 2013 at 10:50 am
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.
October 10, 2013 at 7:33 am
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