September 8, 2014 at 10:48 pm
--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
September 10, 2014 at 4:38 pm
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 TableNameSELECT * 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.
September 11, 2014 at 1:46 pm
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
September 15, 2014 at 5:37 am
Discarded my post as reiterating previous findings.
TomThomson and Hugo Kornelis thank you for yours comments.
Viewing 4 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply