April 12, 2019 at 3:24 pm
It's as much MS fault as anyone for being so inconsistent with when you need a semi colon. For example with CTE's they could have made it so CTE's also had to end with a semi colon but they didn't.
April 12, 2019 at 3:25 pm
Lynn Pettis wrote:The problem is that people are taught to do this as a crutch to insure that the previous statement is terminated by a semicolon instead of being taught to properly terminate their SQL statements. Yes, they were optional but it is getting to the point that they are actually becoming mandatory. Just makes sense to use them properly as terminators instead of beginninators.
Honestly, in my view, people taught to put a semicolon at the start of a CTE is just utter laziness on behalf of the teacher; as they see no good reason to teach people properly. And, in the end, it ends up teaching people wrong (as this user has proved with their syntax
;WITH CTE AS(...), ;CTE2 AS...). Unfortunately these people teaching such standards are a real problem, and are probably the same people who aren't teaching students how to parametrise a query properly in their application. Hence why you still see code likesqlQuery = "SELECT * FROM MyTable WHERE CustomerName = '" & Name.text &"'";on an almost daily occurance on websites like SSC and Stack Overflow...
It also didn't help that when Microsoft introduced CTEs in MS SQL Server the first examples in BOL started CTEs with semicolons as well. Thankfully Microsoft has cleaned up that problem as far as I have been able to determine.
April 12, 2019 at 3:27 pm
It's as much MS fault as anyone for being so inconsistent with when you need a semi colon. For example with CTE's they could have made it so CTE's also had to end with a semi colon but they didn't.
Well, actually you wouldn't end a CTE with a semicolon, but end the final SELECT, INSERT, UPDATE, or DELETE with a semicolon. Oh, wait, you really should be doing that any ways.
And it isn't just Microsoft. The standard had semicolons as optional but the changes to MS SQL Server have actually made using them almost mandatory since some statements require that the previous statement be terminated and other to be terminated with semicolons.
April 12, 2019 at 3:53 pm
Ok one last question. I need to reference the row number in the first Cte which I renamed to make more sense. What I need to do is, anticipate the students end date based on the RowNum in the TermCte by adding 8 to the RowNum which will give me the Students enddate associated with their last term. It may have been answered in a previous post and if It was I apologize.
USE Warehouse
GO
WITH TermCte AS
(SELECT T.Term
,T.TermEnd
,ROW_NUMBER() OVER(ORDER BY T.TermSort) AS RowNum
FROM Term AS T
WHERE RIGHT(T.Term,2) IN ('SP','FA') AND T.TermReportingYear >= 2010
),
StudentCte AS
(SELECT ID
,StuLevelStartTerm
FROM dbo.StudentAcadlevel AS SAL
--WHERE ID = '1221417'
)
SELECT SAL.ID
,SAL.StuLevelStartTerm
,T.Term
FROM dbo.Term AS T
JOIN dbo.StudentAcadlevel AS SAL
ON T.Term = SAL.StuLevelStartTerm;
April 12, 2019 at 3:54 pm
Sorry I forgot to use the Edit
USE Warehouse
GO
WITH TermCte AS
(SELECT T.Term
,T.TermEnd
,ROW_NUMBER() OVER(ORDER BY T.TermSort) AS RowNum
FROM Term AS T
WHERE RIGHT(T.Term,2) IN ('SP','FA') AND T.TermReportingYear >= 2010
),
StudentCte AS
(SELECT ID
,StuLevelStartTerm
FROM dbo.StudentAcadlevel AS SAL
--WHERE ID = '1221417'
)
SELECT SAL.ID
,SAL.StuLevelStartTerm
,T.Term
FROM dbo.Term AS T
JOIN dbo.StudentAcadlevel AS SAL
ON T.Term = SAL.StuLevelStartTerm;
April 12, 2019 at 4:01 pm
ZZartin wrote:It's as much MS fault as anyone for being so inconsistent with when you need a semi colon. For example with CTE's they could have made it so CTE's also had to end with a semi colon but they didn't.
Well, actually you wouldn't end a CTE with a semicolon, but end the final SELECT, INSERT, UPDATE, or DELETE with a semicolon. Oh, wait, you really should be doing that any ways. And it isn't just Microsoft. The standard had semicolons as optional but the changes to MS SQL Server have actually made using them almost mandatory since some statements require that the previous statement be terminated and other to be terminated with semicolons.
Technically you can't have just a CTE WITH clause without some statement tied with it so I would consider the CTE + whatever SELECT/INSERT/UPDATE/DELETE you have to be the complete CTE. But since they're so inconsistent I can understand why teachers would teach from the perspective of this is where you absolutely have to have a semi colon.
April 16, 2019 at 12:35 pm
Yeah, like THROW...try putting one in a BEGIN/END block and see SQL Server complain.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply