February 14, 2012 at 3:21 am
Hugo Kornelis (2/14/2012)
However, it is not true that ;WITH will cause errors when all statements are semicolon terminated. There is no limit to how many semicolons you use and where you place them. The code below, though clearly not recommended coding style, works.
SELECT 1;;;;;;
;;;WITH x AS (SELECT 1 AS a)
SELECT * FROM x
Ah, that is good to know.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 14, 2012 at 4:09 am
Nice question, but the answer is a complete fail :angry:.
The whole batch will in fact fail because a variable can only be declared once ion a batch, so the batch is failed and neither statement is executed. The nearest option to that in the answers is "both fail".
Had the question been "which batch will fail" the given answer would have been correct, but the question was about statements and the code given was a single batch with the same variable declared twice.
Looks like a lack of quality assurance to me :hehe:.
And I'm appalled at all those earlier replies that indicate no-one noticed this simple coding error.
Tom
February 14, 2012 at 4:30 am
Appalled? I usually reserve that reaction for earthquakes, famines, wars and so on.
In any case, I don't agree that just because two statements are posted on the same page without a GO between them, we should necessarily assume that they are to be executed as a single batch. OK, so the question could have said "assuming that they are executed separately", but if we start down that road, we'd end up with a list of terms and conditions longer than the question itself. In my opinion, the intent of this question was clear.
John
February 14, 2012 at 6:26 am
Good question. Got it wrong and thanks to the explanations offered I have learnt something today, which is always a good thing as I have lots to learn.
Thanks to you all.:-D
February 14, 2012 at 6:30 am
While its certainly easier to use brackets/parenthesis around @sqlstring don't forget about sp_executesql.
declare @sqlstring nvarchar (255)
set @sqlstring = 'use ' + 'master' + ' dbcc showfilestats'
execute sp_executesql @sqlstring
February 14, 2012 at 6:33 am
John Mitchell-245523 (2/14/2012)
Appalled? I usually reserve that reaction for earthquakes, famines, wars and so on.In any case, I don't agree that just because two statements are posted on the same page without a GO between them, we should necessarily assume that they are to be executed as a single batch. OK, so the question could have said "assuming that they are executed separately", but if we start down that road, we'd end up with a list of terms and conditions longer than the question itself. In my opinion, the intent of this question was clear.
John
+1 +1 +1
February 14, 2012 at 7:08 am
This is one I have learned the hard way.....it took several errors before I figured it out
February 14, 2012 at 7:19 am
Whew, I haven't had any caffeine this morning, so this one was a toughy. :doze: I only noticed it missing the brackets right before I submitted my answer. Great question, you almost got me!
February 14, 2012 at 7:39 am
bminch (2/14/2012)
While its certainly easier to use brackets/parenthesis around @sqlstring don't forget about sp_executesql.declare @sqlstring nvarchar (255)
set @sqlstring = 'use ' + 'master' + ' dbcc showfilestats'
execute sp_executesql @sqlstring
Hmmmmmmm Very intesting, so learned more than 1 "trick/work around".
Again learned more from the discussion following the QOD than from the QOD itself.
Thanks
February 14, 2012 at 9:09 am
bminch (2/14/2012)
While its certainly easier to use brackets/parenthesis around @sqlstring don't forget about sp_executesql.declare @sqlstring nvarchar (255)
set @sqlstring = 'use ' + 'master' + ' dbcc showfilestats'
execute sp_executesql @sqlstring
I agree this would be the preferred way. Thanks for pointing that out.
February 14, 2012 at 9:43 am
John Mitchell-245523 (2/14/2012)
Appalled? I usually reserve that reaction for earthquakes, famines, wars and so on.In any case, I don't agree that just because two statements are posted on the same page without a GO between them, we should necessarily assume that they are to be executed as a single batch. OK, so the question could have said "assuming that they are executed separately", but if we start down that road, we'd end up with a list of terms and conditions longer than the question itself. In my opinion, the intent of this question was clear.
John
+1
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
February 14, 2012 at 10:26 am
Darn, thought they both would work. Good question. Thanks for submitting.
http://brittcluff.blogspot.com/
February 14, 2012 at 10:27 am
Yep, I got it wrong (technically right) because I was wrapped up in the declaration of variables.
February 14, 2012 at 10:45 am
Good question! I was slowed by my brain's continued refusal to accept SQL's ability to accept statements delimited by nothing but a space, but I did manage to figure it out.
Obligatory pedantic complaint: they're parentheses, not brackets. These are brackets: []. (For completeness, these are braces: {}.)
February 14, 2012 at 10:59 am
bitbucket-25253 (2/14/2012)
Hmmmmmmm Very intesting, so learned more than 1 "trick/work around".Again learned more from the discussion following the QOD than from the QOD itself.
Thanks
That's often the case, and I agree. Good question, and great followup discussion.
Rob Schripsema
Propack, Inc.
Viewing 15 posts - 16 through 30 (of 49 total)
You must be logged in to reply to this topic. Login to reply