What will this query return?

Question

What will this query return?

Viewing 5 posts - 31 through 34 (of 34 total)

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

Hugo Kornelis (9/5/2014)
TomThomson (9/4/2014)
I have written quite a few parsers, and handling what I've described is not at all hard
That comment was not directed at you or your proposal. 🙂
One more reason why I hope that the semicolon will become mandatory in a future version is this snippet that my boss five job changes back used when he had to correct something in production:
--DELETE FROM TableName
SELECT * FROM TableName
WHERE ...
He would work on the WHERE clause until just the right rows were returned, then uncomment the DELETE and comment out the SELECT and hit submit.
Until, one day, he forgot to comment out the SELECT.
With mandatory semicolons, the result would have been an error message, instead of a few panicking colleagues trying to find the most recent backup.

I like that example. It almost convinces me that statement terminators in languages that shouldn't need them could be useful.

But on consideration, I also think that people who develop code by that method on a production (or even shared development test) system instead of on their desktop deserve what they get, provided of course that they get what they deserve. I used the swap from select to delete (or to update sometimes) and back technique a lot on my desktop when I was tech director at Neos Interactive; but never anywhere where it would matter (to anyone but myself) if I made that mistake - with SA (both windows and SQL) access to all our machines including all those on customer sites I was quite careful not to do anything like that.

Tom

Shiva N Hall of Fame Points: 3125 More actions · Answer 2

--Hi, your doubt is absolutely right, because the CTE doesn't need when --it is the 1st batch i the overall query,

--e.g.

create proc usp_Test

as

begin

; whith cte as --here cte is the 1st batch so semicolon is not nessessary

(

select 1 as col

)

select * from cte;

End

--but see the below query, what is importance of semicolon

create proc usp_Test2

as

select * from sales..Location with(nolock)

with cte as -- here cte is the 2nd batch so need semicolon

(

select 1 as col

)

select * from cte;

Thanks,
Shiva N
Database Consultant

Primo Dang SSCrazy Points: 2643 More actions · Answer 3

Hugo Kornelis (9/5/2014)
One more reason why I hope that the semicolon will become mandatory in a future version is this snippet that my boss five job changes back used when he had to correct something in production:
--DELETE FROM TableName
SELECT * FROM TableName
WHERE ...
He would work on the WHERE clause until just the right rows were returned, then uncomment the DELETE and comment out the SELECT and hit submit.
Until, one day, he forgot to comment out the SELECT.
With mandatory semicolons, the result would have been an error message, instead of a few panicking colleagues trying to find the most recent backup.

I usually avoid that issue with code such as this:

select *

-- delete t

from TableName t

where...

Then, instead of changing what part is commented, I select the text from delete to the end and run just that part. Using an alias allows the query to include joins, if that's necessary.

jccencic Grasshopper Points: 10 More actions · Answer 4

The correct is that print CTE and the zero divided operation..

CTE

Msg 8134, Level 16, State 1, Line 3

Divide by zero error encountered.

This is in sqlserver 2008 r2

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 5

Discarded my post as reiterating previous findings.

TomThomson and Hugo Kornelis thank you for yours comments.