Beginning a CTE

Question

Beginning a CTE

Viewing 15 posts - 46 through 60 (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

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.
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 you add a statement using a CTE after a statement that is not properly terminated, go back and properly terminate it. Simple.

Microsoft has deprecated NOT terminating statements with a semicolon. MS SQL Server T-SQL developers should get in the habit of terminating all SQL statements.

Aaron N. Cutshall SSCrazy Eights Points: 9011 More actions · Answer 2

Lynn Pettis (6/6/2016)
Actually, if you add a statement using a CTE after a statement that is not properly terminated, go back and properly terminate it. Simple.

That's what I do to maintain semi-colons in their proper place and to encourage proper usage of them. However, I do understand the intent of the QotD however it was presented.

LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com

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

Aaron N. Cutshall (6/6/2016)
Lynn Pettis (6/6/2016)
Actually, if you add a statement using a CTE after a statement that is not properly terminated, go back and properly terminate it. Simple.
That's what I do to maintain semi-colons in their proper place and to encourage proper usage of them. However, I do understand the intent of the QotD however it was presented.

I don't agree with teaching a crutch.

Toreador SSChampion Points: 11365 More actions · Answer 4

Aaron N. Cutshall (6/6/2016)
However, I do understand the intent of the QotD however it was presented.

What was that intent, to encourage sloppy coding?

Aaron N. Cutshall SSCrazy Eights Points: 9011 More actions · Answer 5

Toreador (6/6/2016)
Aaron N. Cutshall (6/6/2016)
However, I do understand the intent of the QotD however it was presented.
What was that intent, to encourage sloppy coding?

Yes, it's a lazy approach that some do to "begin" their CTE with a semi-colon and I do disagree with that approach. I believe the QotD was to highlight the wrong perception that many people have that a CTE MUST begin with a semi-colon due to the over abundant examples that do just that.

LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com

Aaron N. Cutshall SSCrazy Eights Points: 9011 More actions · Answer 6

Lynn Pettis (6/6/2016)
I don't agree with teaching a crutch.

It's not teaching the crutch, but teaching that the crutch is NOT NECESSARY.

LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com

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

Aaron N. Cutshall (6/6/2016)
Lynn Pettis (6/6/2016)
I don't agree with teaching a crutch.
It's not teaching the crutch, but teaching that the crutch is NOT NECESSARY.

But it is encouraging it.

tom.w.brannon Hall of Fame Points: 3976 More actions · Answer 8

The answer should be No. Open a new window and paste in the following and it works just fine:

with myCTE(n)

as

(select 1

)

select * from myCTE

No semicolons anywhere in the window. I'd like my point, please.

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

tom.w.brannon (6/7/2016)
The answer should be No. Open a new window and paste in the following and it works just fine:
with myCTE(n)
as
(select 1
)
select * from myCTE
No semicolons anywhere in the window. I'd like my point, please.

You just got it by posting. If all you are worried about is the points you can have mine. I tried to turn them in for a free t-shirt but it still doesn't work. 😀

_______________________________________________________________

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/

TomThomson SSC Guru Points: 104773 More actions · Answer 10

Sean Lange (6/7/2016)
tom.w.brannon (6/7/2016)
The answer should be No. Open a new window and paste in the following and it works just fine:
with myCTE(n)
as
(select 1
)
select * from myCTE
No semicolons anywhere in the window. I'd like my point, please.
You just got it by posting. If all you are worried about is the points you can have mine. I tried to turn them in for a free t-shirt but it still doesn't work. 😀

Perhaps these points could be given a suitable value so that people can understand what they are worth: perhaps it wouldn't cause Redgate Software to go bust if each person who succeeds in makeing 10 billion points were to be awarded a tea shirt (not in exchange for the points, they could be allowed to keep them) provided of course that each point expires 100 years after being awarded.

note: a tea shirt is not a T-shirt, unless the T-shirt is made of cloth woven from fibre extracted from used tea leaves.

edit: the trouble with such a scheme is that it grossly exaggerates the value of the points.

Tom

j-1064772 SSCertifiable Points: 5316 More actions · Answer 11

The points are not merely "bragging" points. They are a way to recognize the tremendous effort made so generously by the heavy hitters providing answers and guidance to the entire community.

These points also help me remind myself when I see a contribution from a heavy-hitter to take it seriously even in the case of controversial issues (the Forum have had their share of 20-page long threads).

I've been on this forum for years and I can't even fathom how on earth someone could have amassed 10,000 such "points". How can they even find the time to breathe ?

TomThomson SSC Guru Points: 104773 More actions · Answer 12

I read the latest version of the "correct" answer again, and it's rather amusing.

if we start with two statements and the first is not terminated by a semicolon, if we add one semicolon before the second statement we still have two statements but now the first is terminated by a semicolon. So we don't have a semicolon between two statements.

So in order to add a semicolon between the statements, as required by this answer, we must be adding two semicolons - the one that terminates the first statement and the one which goes in between the two statements.

I don't think that's actually what we need to do to make the second statement (which begins with a CTE) work so it looks to me as if there's still some tweaking needed in the wording.

Tom

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

I still think points should be awarded back, and I don't usually advocate this position.

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

I decided to look at the msdn page referenced and I was amazed!

Not ONE of the sample CTEs was started with a semicolon. The CTE was either the first statement in the batch or the previous statement was PROPERLY terminated with a semicolon.

/https://msdn.microsoft.com/en-us/library/ms175972.aspx

Marcia J SSCertifiable Points: 5649 More actions · Answer 15

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

Thanks. I needed a good laugh. 😀