Beginning a CTE

Question

Beginning a CTE

Viewing 15 posts - 31 through 45 (of 62 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Steve Jones - SSC Editor (6/3/2016)
We can be pedantic if we want. However, a batch consists of 1 or more statements. The option notes that the previous statement is not terminated, so a ; is needed.
Now, are we beginning a CTE or ending the previous statement? We are ending the previous statement, however since there is very, very inconsistent usage of semicolons, often a code line containing a CTE begins with a semicolon.
I knew this question would provoke some comments. However, pedantically, I didn't state this would be part of the CTE statement. However, I did edit the question to this:
Do I always need a semicolon to begin a line that defines a CTE?

I still disagree. The semicolon belongs at the end of the previous statement, not at the start of the CTE.

Sorry, but this is a pet peeve.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

That's fine.

I'll still disagree as well. Given history and the way many samples are given, there is often this code:

...

; with mycTE()

...

I hope to bring to light the reason for the semicolon, which is the previous statement is not terminated.

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 3

Lynn Pettis (6/3/2016)
Steve Jones - SSC Editor (6/3/2016)
We can be pedantic if we want. However, a batch consists of 1 or more statements. The option notes that the previous statement is not terminated, so a ; is needed.
Now, are we beginning a CTE or ending the previous statement? We are ending the previous statement, however since there is very, very inconsistent usage of semicolons, often a code line containing a CTE begins with a semicolon.
I knew this question would provoke some comments. However, pedantically, I didn't state this would be part of the CTE statement. However, I did edit the question to this:
Do I always need a semicolon to begin a line that defines a CTE?
I still disagree. The semicolon belongs at the end of the previous statement, not at the start of the CTE.
Sorry, but this is a pet peeve.

I would prefer this phrasing,

If I run a batch with two or more statements containing a CTE and the CTE is not the first statement do I need a ; between the CTE and the proceeding statement?

That avoids the mess of arguing over whether the ; is ending the previous statement or beginning the CTE as right now functionally statement terminators are not required.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

ZZartin (6/3/2016)
Lynn Pettis (6/3/2016)
Steve Jones - SSC Editor (6/3/2016)
We can be pedantic if we want. However, a batch consists of 1 or more statements. The option notes that the previous statement is not terminated, so a ; is needed.
Now, are we beginning a CTE or ending the previous statement? We are ending the previous statement, however since there is very, very inconsistent usage of semicolons, often a code line containing a CTE begins with a semicolon.
I knew this question would provoke some comments. However, pedantically, I didn't state this would be part of the CTE statement. However, I did edit the question to this:
Do I always need a semicolon to begin a line that defines a CTE?
I still disagree. The semicolon belongs at the end of the previous statement, not at the start of the CTE.
Sorry, but this is a pet peeve.
I would prefer this phrasing,
If I run a batch with two or more statements containing a CTE and the CTE is not the first statement do I need a ; between the CTE and the proceeding statement?
That avoids the mess of arguing over whether the ; is ending the previous statement or beginning the CTE as right now functionally statement terminators are not required.

So that means this:

;THROW

is necessary to ensure that the previous statement is terminated by a semicolon as well. Interesting thing, not one sample in Books Online does this for the THROW statement. The preceding statement in each sample is properly terminated with a semicolon.

Also, the MERGE statement is required to be terminated with a semicolon.

Microsoft should have ensured in all the CTE samples in Books Online had the previous statement terminated with a semicolon and that the WITH did not have a semicolon prepended.

Just my not so humble opinion.

below86 SSChampion Points: 11532 More actions · Answer 5

Lynn Pettis (6/3/2016)
Steve Jones - SSC Editor (6/3/2016)
We can be pedantic if we want. However, a batch consists of 1 or more statements. The option notes that the previous statement is not terminated, so a ; is needed.
Now, are we beginning a CTE or ending the previous statement? We are ending the previous statement, however since there is very, very inconsistent usage of semicolons, often a code line containing a CTE begins with a semicolon.
I knew this question would provoke some comments. However, pedantically, I didn't state this would be part of the CTE statement. However, I did edit the question to this:
Do I always need a semicolon to begin a line that defines a CTE?
I still disagree. The semicolon belongs at the end of the previous statement, not at the start of the CTE.
Sorry, but this is a pet peeve.

+1000 on the pet peeve

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 6

Lynn Pettis (6/3/2016)
ZZartin (6/3/2016)
Lynn Pettis (6/3/2016)
Steve Jones - SSC Editor (6/3/2016)
We can be pedantic if we want. However, a batch consists of 1 or more statements. The option notes that the previous statement is not terminated, so a ; is needed.
Now, are we beginning a CTE or ending the previous statement? We are ending the previous statement, however since there is very, very inconsistent usage of semicolons, often a code line containing a CTE begins with a semicolon.
I knew this question would provoke some comments. However, pedantically, I didn't state this would be part of the CTE statement. However, I did edit the question to this:
Do I always need a semicolon to begin a line that defines a CTE?
I still disagree. The semicolon belongs at the end of the previous statement, not at the start of the CTE.
Sorry, but this is a pet peeve.
I would prefer this phrasing,
If I run a batch with two or more statements containing a CTE and the CTE is not the first statement do I need a ; between the CTE and the proceeding statement?
That avoids the mess of arguing over whether the ; is ending the previous statement or beginning the CTE as right now functionally statement terminators are not required.
So that means this:
;THROW
is necessary to ensure that the previous statement is terminated by a semicolon as well. Interesting thing, not one sample in Books Online does this for the THROW statement. The preceding statement in each sample is properly terminated with a semicolon.
Also, the MERGE statement is required to be terminated with a semicolon.
Microsoft should have ensured in all the CTE samples in Books Online had the previous statement terminated with a semicolon and that the WITH did not have a semicolon prepended.
Just my not so humble opinion.

I also would have preferred if MS had also required that all statements requiring the previous statement to end with a ; had to end with a ; themselves. That would have driven the point home pretty clearly that ; goes at the end of a statement not the beginning.

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

Quite logical that why the semi-colon is available...

Thanks.

TomThomson SSC Guru Points: 104773 More actions · Answer 8

Steve Jones - SSC Editor (6/3/2016)
That's fine.
I'll still disagree as well. Given history and the way many samples are given, there is often this code:
...
; with mycTE()
...
I hope to bring to light the reason for the semicolon, which is the previous statement is not terminated.

That of course is pure nonsense. If you have a line like that, the previous statement is terminated by a semicolon - either some semicolon before the one in the code you've quoted or by the one you have quoted.

If you had said "was not terminated before this line" that would have been correct. But now you are making an incorrect statement to bolster up your TRICK QUESTION.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 9

Steve Jones - SSC Editor (6/3/2016)
We can be pedantic if we want. However, a batch consists of 1 or more statements. The option notes that the previous statement is not terminated, so a ; is needed.
Now, are we beginning a CTE or ending the previous statement? We are ending the previous statement, however since there is very, very inconsistent usage of semicolons, often a code line containing a CTE begins with a semicolon.
I knew this question would provoke some comments. However, pedantically, I didn't state this would be part of the CTE statement. However, I did edit the question to this:
Do I always need a semicolon to begin a line that defines a CTE?

We can indeed be pedantic if we want to. You appear to be trying to be. You are not succeeding.

The question doesn't note anything of the kind. You are presumably claiming that the third option notes that: it doesn't. If it did, it would be saying that a batch could never be begun by a statement involving a CTE, because there must be a previous statement, and surely you didn't intend to tell us that that or to claim that an option that said that could possibly be correct?

Please stop trying to be pedantic and failing, it's silly and irritating. And the whole discussion from your side smacks of intent to trick instead of intent to provide a good question.

Tom

sknox SSChampion Points: 12388 More actions · Answer 10

This (and MERGE) is why I have begun the habit of separating my statements with semicolons on their own line:

BEGIN

MERGE INTO

....

;

WITH CTE AS ...

;

END

This makes the semicolons more obvious, and by spacing after but not before, we see that they belong with the previous statement. It also has the benefit of allowing you to modify the first statement without having to worry as much about whether you accidentally deleted the semicolon.

Without this separation, the meaning of the semicolon becomes blurred.

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

And let's consider the table valued function that uses a cte. If you slap a semicolon at the beginning doesn't that now make what could have been an inline table valued function a multi statement table valued function? Haven't actually explored the performance impact but it certainly is interesting.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 12

Sean Lange (6/6/2016)
And let's consider the table valued function that uses a cte. If you slap a semicolon at the beginning doesn't that now make what could have been an inline table valued function a multi statement table valued function? Haven't actually explored the performance impact but it certainly is interesting.

Alas (or actually thankfully) not. ITVF definitions start [font="Courier New"]RETURNS TABLE[/font], whereas MTVFs start [font="Courier New"]RETURNS @return_variable TABLE <table_type_definition>[/font], and there are other differences in syntax, too.

I didn't think the question was tricky or pedantic. It's just pointing out that if you don't terminate the previous statement, you have to put a semicolon in front of the CTE definition. Pet peeve or not, and regardless of what the documentation says or is silent on, that's perfectly legal.

John

Terje Hermanseter SSCarpal Tunnel Points: 4851 More actions · Answer 13

John Mitchell-245523 wrote (6/6/2016): I didn't think the question was tricky or pedantic. It's just pointing out that if you don't terminate the previous statement, you have to put a semicolon in front of the CTE definition.
John

Simple as that. That's how I conceived it too.

Sean Lange SSC Guru Points: 286590 More actions · Answer 14

John Mitchell-245523 (6/6/2016)
Sean Lange (6/6/2016)
And let's consider the table valued function that uses a cte. If you slap a semicolon at the beginning doesn't that now make what could have been an inline table valued function a multi statement table valued function? Haven't actually explored the performance impact but it certainly is interesting.
Alas (or actually thankfully) not. ITVF definitions start [font="Courier New"]RETURNS TABLE[/font], whereas MTVFs start [font="Courier New"]RETURNS @return_variable TABLE <table_type_definition>[/font], and there are other differences in syntax, too.

Good point. I never use the multi statement version so the syntax is not as familiar. 😉

I didn't think the question was tricky or pedantic. It's just pointing out that if you don't terminate the previous statement, you have to put a semicolon in front of the CTE definition. Pet peeve or not, and regardless of what the documentation says or is silent on, that's perfectly legal.
John

Actually if we really want to get down to being pedantic the question states "Do I always need a semicolon to begin a line that defines a CTE?". By definition this is not possible because a semicolon terminates a line. If you "start" a line with a semicolon you also definition have just ended it. What it is really pointing out is that if the previous statement was not terminated that the convention for many people is to put one in front of the cte. What this really is doing though is terminating the previous line.

To add to that, the documentation in the link provided in the answers states this:

When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

It states that exact sentence twice.

I fully agree that many people use the leading semicolon as "safety check". And of course it is syntactically acceptable because you can have as many line terminators as you want. This is perfectly valid t-sql ;;;;;;;;;;;;;;;

What is one more semicolon among ctes? 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Terje Hermanseter SSCarpal Tunnel Points: 4851 More actions · Answer 15

Sean Lange (6/6/2016)
What is one more semicolon among ctes? 😀

#MakeSemicolonsGreatAgain