July 11, 2018 at 9:50 pm
Comments posted to this topic are about the item Starting a CTE
July 11, 2018 at 9:51 pm
I am so looking forward to the arguments about where the semicolon should go exactly.
July 11, 2018 at 11:40 pm
Nice easy one, thanks Steve.
Given that I have been coding in C# quite a bit lately, it has become my habit to place the semi-colon at the end of each statement....
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
July 12, 2018 at 1:13 am
Any code before the "WITH STATEMENT" must end with a terminator, which is the semi colon. This prevents errors if this code is placed after other statements in a batch.
As in: Not each CTE needs a semicolon. You only need one. (And it is optional when it is the first statement in the batch.)
July 12, 2018 at 1:24 am
If you need to resort to this, you have a very bad coding practice.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 12, 2018 at 5:36 am
Nice easy one, thanks Steve!
---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know
July 12, 2018 at 6:54 am
Koen Verbeeck - Thursday, July 12, 2018 1:24 AMIf you need to resort to this, you have a very bad coding practice.
Or you deal with legacy code.
July 12, 2018 at 7:13 am
RoNoS - Thursday, July 12, 2018 1:13 AMAny code before the "WITH STATEMENT" must end with a terminator, which is the semi colon. This prevents errors if this code is placed after other statements in a batch.
As in: Not each CTE needs a semicolon. You only need one. (And it is optional when it is the first statement in the batch.)
Actually the semicolon does not even belong to the cte, it is terminating the previous statement. A CTE requires that the previous statement is terminated with a semicolon. It is very common for people to put them at the beginning to ensure the previous statement was terminated.
_______________________________________________________________
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/
July 12, 2018 at 7:15 am
Was helping somebody just yesterday on another site and they couldn't for the life of them figure out why their view was throwing a syntax error. The view started with a cte that had a leading semicolon. They claimed they had been wrestling the thing for a couple of days. I used Lynn's terminology that the semicolon is not a beginninator, but a terminator. And once they removed the semicolon their view was just fine. I don't know why the coding style of starting a cte became to be but it causes so much confusion.
_______________________________________________________________
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/
July 12, 2018 at 7:45 am
Koen Verbeeck - Thursday, July 12, 2018 1:24 AMIf you need to resort to this, you have a very bad coding practice.
Testify!
One of my largest complaints about T-SQL is that Microsoft decided that the statement terminator operator would be optional.
Officially not using semicolons to terminate every T-SQL statement is has been deprecated since the release of SQL Server 2008, "Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version."
The smart money is on Microsoft never making statement terminators mandatory. The amount of T-SQL code that would break is mind boggling.
Nevertheless, everyone coding T-SQL should be using semicolons to terminate every statement.
July 12, 2018 at 7:49 am
Sean Lange - Thursday, July 12, 2018 7:15 AMI don't know why the coding style of starting a cte became to be but it causes so much confusion.
It came to be because Microsoft made the decision to not enforce the ANSI SQL-92 standards in SQL Server that require each valid SQL statement to be terminated with a semicolon.
So putting a semicolon at the beginning of any CTE became a bit of defensive programming for those who have been around the block and one of those things you just do for people that haven't.
July 12, 2018 at 7:58 am
lmalatesta - Thursday, July 12, 2018 7:45 AMThe smart money is on Microsoft never making statement terminators mandatory. The amount of T-SQL code that would break is mind boggling.
Not taking that bet. They'll never require it
July 12, 2018 at 7:59 am
Also, it's not all code. You can do
CREATE PROCEDURE myproc
AS
BEGIN
WITH cte
AS
(
SELECT *
FROM dbo.SomeTable
WHERE SomeColumn = 'A'
)
SELECT *
FROM cte;
END;
July 12, 2018 at 8:04 am
lmalatesta - Thursday, July 12, 2018 7:49 AMSean Lange - Thursday, July 12, 2018 7:15 AMI don't know why the coding style of starting a cte became to be but it causes so much confusion.It came to be because Microsoft made the decision to not enforce the ANSI SQL-92 standards in SQL Server that require each valid SQL statement to be terminated with a semicolon.
So putting a semicolon at the beginning of any CTE became a bit of defensive programming for those who have been around the block and one of those things you just do for people that haven't.
Even worse than not enforcing it is that they DO enforce it but not for everything. Only some statements require the previous one to be properly terminated and only some statements require that they themselves are terminated. So much sloppy code out there because of the lack of requirements for so long they are really kind of stuck. They want to enforce standards but it would break so much code out there.
_______________________________________________________________
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/
July 12, 2018 at 9:15 am
Sean Lange - Thursday, July 12, 2018 7:15 AMWas helping somebody just yesterday on another site and they couldn't for the life of them figure out why their view was throwing a syntax error. The view started with a cte that had a leading semicolon. They claimed they had been wrestling the thing for a couple of days. I used Lynn's terminology that the semicolon is not a beginninator, but a terminator. And once they removed the semicolon their view was just fine. I don't know why the coding style of starting a cte became to be but it causes so much confusion.
Old examples in Books Online. The newer Microsoft documentation doesn't do it, at least the examples I have seen lately.
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply