CTE Syntax

Question

Post reply

CTE Syntax

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
March 29, 2015 at 11:13 am

#72524

Comments posted to this topic are about the item CTE Syntax
March 30, 2015 at 4:58 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 64 total)

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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 1

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.

Thanks.

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 2

Thanks for the question Steve!

Now I can see yet another discussion on Terminators vs. Beginingnators coming, must say I'm with the latter as my muscle memory punches a semicolon whenever I'm about to write ;WITH:-D

😎

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 3

Good question. Also it's a general recommendation to use semi-colons for all statements in t-sql codes. People usually do not always. Thanks.

Igor Micev,My blog: www.igormicev.com

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 4

Such a bad habit to start a CTE with

;WITH

I hate it 😀

Nice question though, thanks Steve.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Koen Verbeeck (3/30/2015)
Such a bad habit to start a CTE with
;WITH
I 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

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sipas Hall of Fame Points: 3828 More actions · Answer 6

Koen Verbeeck (3/30/2015)
Such a bad habit to start a CTE with
;WITH
I 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.

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 7

You could find a possible explanation in this link

As I am registered for CodeProject , I have already read this article and I got easily the point.

I have also heard a person who explained that he was always beginning his code with a ; to avoid any conflict with a previous statement in case of a copy/paste of T-SQL code ( I am applying this rule which seems to a good one )

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 8

patricklambin (3/30/2015)
You could find a possible explanation in this link
As I am registered for CodeProject , I have already read this article and I got easily the point.
I have also heard a person who explained that he was always beginning his code with a ; to avoid any conflict with a previous statement in case of a copy/paste of T-SQL code ( I am applying this rule which seems to a good one )

Why don't you start your sentences/documents/emails with a . as well?

Just in case someone copy pastes your text 😉

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Toreador SSChampion Points: 11358 More actions · Answer 9

The correct answer is (e) because questions like this promote sloppy coding

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 10

;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.

😎

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

.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

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

.dup

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Toreador SSChampion Points: 11358 More actions · Answer 13

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.
😎

.Yes that's true

;but it doesn't make it a good way to code

.Surely we should be promoting good practice

, rather than something random that happens to work

?