April 30, 2013 at 12:09 am
April 30, 2013 at 12:17 am
A nice one! I learnt something new, even if i wouldn't use this behaviour.
I'll still declare every variable as before. It's much easier to read and understand for me.
April 30, 2013 at 1:26 am
Sergiy (4/29/2013)
Thanks everyone for positive feedback.As you can guess the question came from practical experience when developers were puzzled with "odd" behaviour of SQL Server.
Had to build this sample script to make the matter clear.
Then it appeared to me other might benefit from it too.
Yep, nice one.
_______________________________________________
www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)
April 30, 2013 at 2:19 am
Nice fun question, clear explanation, back to basics of variable declarations.
Of course MS have got the documentation wrong yet again - this is another one of those. The the IF condition @ToCreate = 1 returns neither false nor true, so according to the introductory paragraph of the IF...ELSE BOL page neither the THEN nor the ELSE branch is executed and @Table should remain empty. Of course this doesn't happen, it's a bug in the documentation. I added some community content to the 2008 R2 page to point this out, but probably it won't get corrected.
Tom
April 30, 2013 at 4:11 am
This was removed by the editor as SPAM
April 30, 2013 at 4:22 am
I found this interesting and was unaware of this property, which is not like other code environments / execution. I won't be taking advantage, but knowing it does mean I will not waste time trying to hide declarations out of scope or something.
April 30, 2013 at 4:41 am
What made this curious from my point of view is that normally you cannot have an empty code block in the various parts of an IF - the compiler throws it out. e.g.
IF @var = 1 BEGIN
END
ELSE BEGIN
SET @var += 1
END
This could often be useful in cases where either the code is in development and incomplete, or you want to stop whatever is done in that block during testing, or the test condition is complex and defines the only case when you don't want to do whatever comes next, or blah blah ...
Here, it has been noted, the DECLARE is non-executable code, and so at run-time the IF effectively contains an empty branch. So, hey!!
IF @var = 1 BEGIN
DECLARE @dummy bit -- my version of the no-op!
END
ELSE BEGIN
SET @var += 1
END
I don't really like it, but it could be useful sometimes. More SQL Server weirdness.
April 30, 2013 at 5:47 am
Easy one, as I was bitten by this before. Thanks for the question.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 30, 2013 at 6:34 am
Thank you, I learned something this morning.
April 30, 2013 at 6:35 am
Bob Cullen-434885 (4/30/2013)
Here, it has been noted, the DECLARE is non-executable code, and so at run-time the IF effectively contains an empty branch.
And if @ToCreate is set to 1, SQL doesn't get tripped up on the empty branch at all.
So here's a question. If variables are initialized at parsing time - I take this to mean that the DECLARE is interpreted prior to the INSERT - why can't you declare a variable later in the code than it's used? You can't swap out the IF and ELSE branches in this example. Not that you'd want to do it, it's just an academic question.
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
April 30, 2013 at 6:41 am
Good point, Ron.
My guess is that the DECLARE must be encountered by the parser before it encounters the variable being used, so that it already "knows" about it, even though nothing is being executed yet. This is typical of a single-pass compiler that does not do post-parse fix-ups and the like (yeah - I kinda wrote one once, back on a 1970's PDP-11 in assembler and wished I hadn't!!)
April 30, 2013 at 6:50 am
Thanks for the good question. Learned something new today, and that is why I like the QotD!!
April 30, 2013 at 7:16 am
Good to know before trying to use conditional declarations.
April 30, 2013 at 7:18 am
Nice question. I thought it would have error out based on my experience with other programming languages. Glad I found this out before I ever got stuck trying to figure out what is wrong.
Thanks!
April 30, 2013 at 7:21 am
L' Eomot Inversé (4/30/2013)
Nice fun question, clear explanation, back to basics of variable declarations.Of course MS have got the documentation wrong yet again - this is another one of those. The the IF condition @ToCreate = 1 returns neither false nor true, so according to the introductory paragraph of the IF...ELSE BOL page neither the THEN nor the ELSE branch is executed and @Table should remain empty. Of course this doesn't happen, it's a bug in the documentation. I added some community content to the 2008 R2 page to point this out, but probably it won't get corrected.
I don't think I follow you here Tom. The condition checks is @ToCreate = 1. It does not equal 1 because it is NULL. Therefore the boolean check would be false. How could it return unknown? It is very clearly known that @ToCreate is not 1.
Sergiy, great question!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 16 through 30 (of 60 total)
You must be logged in to reply to this topic. Login to reply