You may have noticed in my code examples, I always include a
semicolon ";" at the end of my TSQL statements. If you're not doing this, then you need to
start now. Actually, if you're not
writing TSQL code as a DBA then you need to start now.
Starting in SQL Server 2005, Microsoft introduced the TSQL
terminator. This syntax is not required
for most statements, but at some point in the future it will be required for all. You can read the details on MSDN. As with all new changes to SQL, you should be
future-proofing your code.
Some statements already require the use of the
terminator. One of the best examples is
the use of a Common Table Expression (CTE).
If you attempt to run this code below, you will get a syntax error.
USE
AdventureWorks2008R2
WITH CTE
AS (
SELECT
P.FirstName
,P.LastName
,E.VacationHours
FROM HumanResources.Employee E
JOIN Person.Person P
ON E.BusinessEntityID = P.BusinessEntityID
)
SELECT * FROM CTE
WHERE
VacationHours > 40;
GO
Msg 319, Level 15,
State 1, Line 3
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.
To start defining a CTE, the WITH statement must be the
first statement in a batch or the preceding statement must be terminated with a
semicolon, even if it's something as simple as a USE DATABASE statement.
Add the semicolon to the end of the USE statement and rerun,
and you'll get the results without any errors.
USE
AdventureWorks2008R2;
WITH CTE
AS (
SELECT
P.FirstName
,P.LastName
,E.VacationHours
FROM HumanResources.Employee E
JOIN Person.Person P
ON E.BusinessEntityID = P.BusinessEntityID
)
SELECT * FROM CTE
WHERE
VacationHours > 40;
GO
(178 row(s) affected)
This is one of the easiest code changes you can make, so
starting adding it to your code today and save yourself the hassle of rewriting it tomorrow.
No comments:
Post a Comment