March 30, 2015 at 5:51 am
Eirikur Eiriksson (3/30/2015)
;With due respect, additional and extra semicolons between statements will not cause any problems and CTE statements will run, without the semicolon it will not.😎
Not terminating T-SQL statements with a semicolon is a deprecated feature (I think from SQL Server 2012); shouldn't everyone be encouraged to terminate their statements correctly? 🙂
March 30, 2015 at 6:08 am
SQL-DBA-01 (3/29/2015)
Thanks Steve for the nice question..as usual.I explored more on the cause.
•You must remember to terminate the statement preceding the CTE with a semicolon, otherwise SQL will throw this error message at you:
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
+ 1
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
March 30, 2015 at 6:38 am
What's interesting to me is the usage of 'most'. I suspected c was right (since a and d were both wrong), but went looking to prove it. And out of 9 articles on MSDN and stackoverflow, only one started a CTE statement with a semicolon.
Maybe it's just the way Dr. Google parsed my search, but that confused me because it was definitely not 'most'.
March 30, 2015 at 6:42 am
Add me to the list of people who understand the difference between a terminator and a begininator. A statement terminator belongs at the end of a statement and nowhere else. People are capable of learning, so teach them to do it the right way from the start.
March 30, 2015 at 7:04 am
GilaMonster (3/30/2015)
Koen Verbeeck (3/30/2015)
Such a bad habit to start a CTE with
;WITHI hate it 😀
.This .It's a statement terminator .It has no place at the beginning of statements .I can't imagine anyone who writes front-end languages (C#, Java, C++, etc) ever even considering starting a statement with a terminator ?So why are we being so difficult about it and writing code that suggests to someone reading later that we don't even know the basic principals of programing languages
I agree that treating it as a statement starter is just plain crazy. And results in ugly-looking code.
But I also believe that if languages need to indicate statement boundaries it is better do it with a separator, not a terminator - it's only a small difference (in the case of C# it would simply remove the need for ";" before "}" so that there would no longer have to be some block closing brackets with a semicolon becfore them and some without) but it's something I've preferred for decades (I never understood why the C-languages broke from the separator-not-terminator tradition that all earlier languages based on Algol had followed, and became teh first Algol-family language with a terminator). Maybe it's just because I preferred the languages (apart from assembly languages) that I used most often my first years of writing software.
Tom
March 30, 2015 at 7:09 am
GilaMonster (3/30/2015)
.But starting statements with terminators teaches bad habits .For example I had a dev recently who scattered ; randomly until the SQL proc started working .Once it had been explained that the ; is a terminator just like in C# he put them correctly and asked me "Then why do people put them at the beginning of statements? That's just stupid." (.His words) .He'd read up on CTEs online and concluded ,based on some people putting the ; at the end and others at the beginning ,that it wasn't a statement terminator ,that it was just some command that no one explained what it did and that followed rules for placing that no one explained?How about we just teach people correctly instead
Eirikur Eiriksson (3/30/2015)
;With due respect, additional and extra semicolons between statements will not cause any problems and CTE statements will run, without the semicolon it will not.😎
.Without ; C# code won't work .We don't see samples of C# code starting with statement terminators
+1.
;WITH makes sense to anyone who has been around a while, but without an explanation those newer to t-SQL (i.e. "accidental dba's") pick it up as "standard practice". By the time they figure it out, much code has been written and it isn't likely to get changed. I'd much prefer to see example code akin to:
-- a CTE must either be the first statement in a batch, or the previous statement must be terminated with a semi-colon or an END
[previous statement];
WITH ...
Ultimately, the responsibility for teaching good practices falls on those who know good practices.
March 30, 2015 at 7:19 am
I've never yet learned to terminate a statement with a semi-colon and never failed to start the WITH without it.
March 30, 2015 at 7:24 am
Iwas Bornready (3/30/2015)
I've never yet learned to terminate a statement with a semi-colon and never failed to start the WITH without it.
Then it's a perfect time to start fixing those bad habits.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 30, 2015 at 7:31 am
Iwas Bornready (3/30/2015)
I've never yet learned to terminate a statement with a semi-colon and never failed to start the WITH without it.
Shame on your teacher 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 30, 2015 at 7:33 am
GilaMonster (3/30/2015)
Koen Verbeeck (3/30/2015)
Such a bad habit to start a CTE with
;WITHI hate it 😀
.This .It's a statement terminator .It has no place at the beginning of statements .I can't imagine anyone who writes front-end languages (C#, Java, C++, etc) ever even considering starting a statement with a terminator ?So why are we being so difficult about it and writing code that suggests to someone reading later that we don't even know the basic principals of programing languages
.It hurts my eyes to see examples using a semi-colon to begin a CTE
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
March 30, 2015 at 7:37 am
sipas (3/30/2015)
Koen Verbeeck (3/30/2015)
Such a bad habit to start a CTE with
;WITHI hate it 😀
Nice question though, thanks Steve.
+1 - maybe part of the answer should be promoting the correct way to write code, rather than the nasty workaround. Good question though.
Give several answer options such as:
;WITH somecte ...
WITH somecte ...
BEGIN
WITH Seomcte ...
END
...
GO
WITH somecte...
Then ask them to pick the 1 that is WRONG.
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
March 30, 2015 at 7:40 am
Eirikur Eiriksson (3/30/2015)
;With due respect, additional and extra semicolons between statements will not cause any problems and CTE statements will run, without the semicolon it will not.😎
A CTE will run without a semi-colon too. It just fails when/if there are multiple code segments in the same batch that have not been properly terminated.
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
March 30, 2015 at 7:42 am
Iwas Bornready (3/30/2015)
I've never yet learned to terminate a statement with a semi-colon and never failed to start the WITH without it.
Not terminating statements with a semicolon is not only poor coding practice, it's also deprecated. In some future version, you'll be required to terminate your statements with a semicolon if you want them to work. In my opinion, this is as it should be.
March 30, 2015 at 7:51 am
Koen Verbeeck (3/30/2015)
;WITH
I hate it :-D/quote]
I totally agree! This leads to sloppy code development and debugging nightmares. Besides, it's ANSI standard to use semi-colon terminators and although SQL Server doesn't impose that at this time, it's a good habit to get into especially when you have to work in other RDBMS environments as well that do require them.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
March 30, 2015 at 8:24 am
Eirikur Eiriksson (3/30/2015)
;With due respect, additional and extra semicolons between statements will not cause any problems and CTE statements will run, without the semicolon it will not.😎
I beg to differ.
IF 'You'='Say so'
;WITH CTE AS(SELECT 1 n)
SELECT *
FROM CTE
GO
CREATE VIEW WillItBreak
AS
;WITH CTE AS(SELECT 1 n)
SELECT *
FROM CTE
GO
Viewing 15 posts - 16 through 30 (of 64 total)
You must be logged in to reply to this topic. Login to reply