September 4, 2014 at 6:48 am
I guess I'm one of the few just getting the msg 8134 when I run this? Running on 2008.
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
September 4, 2014 at 7:26 am
SqlMel (9/4/2014)
Thanks for the question.I'm actually surprised so many people have got this wrong.
I say that because when I started using CTEs I was not accostumed to using the semicolon so I would ALWAYS get this error. I'm sure it happened to a lot of other folks when learning this feature.
+1 I can't tell you how many times that silly little semicolon bit me because I forgot to put it in there...now I'm just in the habit of putting one in front of the with whenever i start to write a new one. Great question, thanks!
September 4, 2014 at 7:28 am
Good question - thanks for sharing.
I'm curious how many shops out there have adopted ;WITH as a standard practice?
We have purposefully avoided it here in favor of terminating all applicable statements instead. My personal opinion is ;WITH is lazy coding. :alien:
September 4, 2014 at 7:55 am
Carlo Romagnano (9/4/2014)
pmadhavapeddi22 (9/4/2014)
When I work with CTE, I get this error when I dont give smicolon, so it was easy to answer 🙂Thanks for the question
+1
+1 again
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
September 4, 2014 at 7:56 am
Dana Medley (9/4/2014)
SqlMel (9/4/2014)
Thanks for the question.I'm actually surprised so many people have got this wrong.
I say that because when I started using CTEs I was not accostumed to using the semicolon so I would ALWAYS get this error. I'm sure it happened to a lot of other folks when learning this feature.
+1 I can't tell you how many times that silly little semicolon bit me because I forgot to put it in there...now I'm just in the habit of putting one in front of the with whenever i start to write a new one. Great question, thanks!
Thanks for the trippy avatar - I thought it was a real bug on my monitor!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
September 4, 2014 at 8:50 am
DrKiller (9/4/2014)
I think the explanation need some work.CTEs doesn't ALWAYS need a semi-colon in front.
This for example works fine:
DECLARE @tmpVariable INTWITH cte
AS
(
SELECT 1/0 as 'Result'
)
SELECT *
FROM cte
I'd like to know when a CTE needs semi-colon and not... I always though its only after a select from a table, but apparently not 🙂
In this case, the variable is discarded from the execution plan so the rest of the statement is considered an entire batch and therefore does not require a semi-colon since there is no other statement to terminate as far as the QO is concerned.
If on the other hand you use that variable and set a value to it, then we have a separate statement that must be terminated with a semi-colon or needs to be contained in a separate batch in some way.
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 4, 2014 at 9:31 am
I wonder if people would be surprised by the error in the following query.
SELECT 1/0 as Result
WITH cte
AS
(
SELECT 'Result' AS Result
)
SELECT *
FROM cte
The reason is simple and has been explained previously, but I'm not sure if most people are aware of it.
September 4, 2014 at 9:51 am
Thanks for the nice, simple question, Shiva about something that trips up a lot of people.
I don't understand why MS requires the semicolon here when it's optional in most other places. You would think the query optimizer could figure this out. Oh, well.
September 4, 2014 at 11:13 am
Nice clear question.
It's also a neat demonstration of how idiotic it was to use rge word "with" to introduce a CTE definition. There were other possibilities, some of them more accurately descriptive than "with", which wouldn't have led to a situation where the parser sometimes needs a preceding terminator to recognise the CTE the structure if the statement including teh CTE is not the frist in the batch, and sometimes doesn't need a terminator (unless "begin" counts as a statement terminator, of course, which seems somewhat idiotic considering that "end" doesn't count as a statement terminator).
Since some people will not understand, here is an example:-
select 'This is utterly stupid!'
begin
with x as (select i from tally where i<3)
select i*i from x
end
The statement beginning "with" is not the first statement in the batch, the first statement in the batch is "select 'This is utterly stupid' " and neither it nor anything else in the batch contains a statement terminator, but despite the stated requirement for a terminator when the statement using a CTE is not first in the batch there is no error here. I picked the message selected by the first statement because it encapsulates my view of this idiotic piece of language misdesign (I imagine of course that Celko would claim it's eminently sensible design).
But this
select 'This is utterly stupid!'
begin
end
with x as (select i from tally where i<3)
select i*i from x
does produce an error, despite containing exactly the same statements in exactly the same sequence as the previous example (begin and end are control-of-flow keywords, not statements) so we have to conclude that "begin" is a statement terminator but "end" isn't.
These examples (and there are plenty more) are of course pretty sillyand pretty trivial; but good language design doesn't give people (not even people with a lot of succesful language design under their belt) opportunities to demonstrate design stupidities with silly trivial examples.
Tom
September 4, 2014 at 12:20 pm
DrKiller (9/4/2014)
I think the explanation need some work.CTEs doesn't ALWAYS need a semi-colon in front.
This for example works fine:
DECLARE @tmpVariable INTWITH cte
AS
(
SELECT 1/0 as 'Result'
)
SELECT *
FROM cte
That's because a DECLARE is not officially a statement. It is actually just information for the parser. One of my recent questions has a longer explanation (focused mostly on the combination of declaration and assignment).
I'd like to know when a CTE needs semi-colon and not... I always though its only after a select from a table, but apparently not 🙂
You should start thinking about it differently. A CTE does not require a semicolon in front of it, every statement requires a semicolon delimiting it. This has been optional for a long time, but that feature (the optionality) has been put on the deprecated list almost a decade ago now.
(And even though DECLARE is not officially a statement, it too can and should be terminated with a semicolon).
Dana Medley (9/4/2014)
now I'm just in the habit of putting one in front of the with whenever i start to write a new one.
That's a terrible habit. Not just lazy, as someone else writes, but dangerous. Try the below:
IF 1 = 1
;WITH TheCTE AS (SELECT 1 AS a)
SELECT * FROM TheCTE;
This gives an error. There can not be a semicolon between IF and the conditional statement. If you are into the habit of putting a semicolon before every WITH, you will run into this. And then you have to accept an inconsistent coding standard. Better to just adapt to the correct coding standard of ending every statement with a semicolon. Especially since omitting that semicolon is a deprecated feature.
stephen.long.1 (9/4/2014)
I don't understand why MS requires the semicolon here when it's optional in most other places. You would think the query optimizer could figure this out. Oh, well.
Have you ever tried to build a parser? I can tell you, that's hard - even in a language with mandatory statement terminators. I can imagine how many engineering hours were lost on every new feature when the semicolon was stull fully optional.
I fully support both Microsoft's decision to make deprecate omitting the semicolon, it will save a lot of engineering hours - that can then be spent on building new features for us.
When Microsoft announced that the optional semicolon would be deprecated, they also announced that they would still support omitting the semicolon for existing code (for now), but not invest serious effort in making this work for new features. Like the CTE (introduced in SQL 2005). Or MERGE (introduced in SQL 2008), which will produce an error if it is not terminated with a semicolon.
TomThomson (9/4/2014)
But this
select 'This is utterly stupid!'begin
end
with x as (select i from tally where i<3)
select i*i from x
does produce an error
Yes, and that error is caused by BEGIN END with no statement in between. If you remove the CTE, you will still get an error, or multiple errors. The first error is caused by "end" directly following "begin", the rest is caused by the parser being off track - the last error will be cause by missing an "end" so that not all "begin" blocks are balanced.
However, you are right that there is inconsistency in the syntax. When the semicolon became mandatory, I wanted to teach myself a consistent style. I did not know whether I should consider BEGIN and END as statements to be terminated, or as block indicators that are not terminated (after all, you would not semicolon-terminate the { and } tokens in C# either).
But this code fails:
IF 1 = 1
BEGIN
SELECT 1;
END
WITH TheCTE AS (SELECT 1 AS a)
SELECT * FROM TheCTE;
so apparently an END has to be terminated. However, this code also fails:
IF 1 = 1
BEGIN TRY;
SELECT 1;
END TRY;
BEGIN CATCH;
SELECT 1;
END CATCH;
The semicolon between END TRY and BEGIN CATCH is invalid. So we are stuck with a situation where END requires a semicolon, END TRY does not permit it, and END CATCH does require it again (not including the code to test this).
I think I also once had a situation where I had an error when I didn't follow BEGIN with a semicolon but I lost that repro and I don't know if it still persists. But I like consistency so I treat BEGIN the same as END.
My termination rule is now:
Terminate every statement (including DECLAREs)
Terminate every BEGIN and every END
Terminate every BEGIN TRY, BEGIN CATCH, and END CATCH
Sob every time I have to type END TRY and cannot put a semicolon.
September 4, 2014 at 6:10 pm
Good analysis, Hugo, but personally I regard terminating "begin" as an abomination - after all, it is not a statement but a bracket indicating that a compound statement begins here. A terminator following "end" is less objectionable (provided it's not misunderstood to be terminating end, which would clearly be objectionable), since if all statements are terminated it is not unreasonable to require compound statements to be terminated, although many Algol-like languages have not required this. It also seems reasonable to say that there is no requirement for a separate terminator for a statement which is immediately followed by "end", since terminating the compound statement of which it is part should be enough.
I have written quite a few parsers, and handling what I've described is not at all hard - it needs nothing that a 1960s era BNF-generated parser couldn't handle.
I am quite happy to assert that there would have been no need at all for a statement terminator (or indeed a statement separator0 in SQL if sensible keywords had been used instead of reusing "WITH" in a ridiculously overloaded manner that not only makes the language harder to parse and introduces the terminator requirement but also makes the language's syntax more difficult for human beings to understand. You don't have to take my word for it, of course, but people who know my track record on language design probably would.
Tom
September 4, 2014 at 10:15 pm
dwhitley (9/4/2014)
I guess I'm one of the few just getting the msg 8134 when I run this? Running on 2008.Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
If the database is in compatability mode 80, you get that error. For mode 90 and above, its the missing ; error...
September 4, 2014 at 11:07 pm
Easy....
September 5, 2014 at 12:27 pm
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.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply