Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Everyday SQL

Patrick Keisler is a MCTS and MCP. For over 12 years, he has been been a database administrator for a major investment bank, Wells Fargo Securities. During that time, he has gained considerable knowledge in Microsoft SQL Server by supporting hundreds of applications ranging from high-volume trading applications to massive data warehouses.

T-SQL Terminator

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.

USEAdventureWorks2008R2

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
WHEREVacationHours > 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.

USEAdventureWorks2008R2;

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
WHEREVacationHours > 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.

Comments

Leave a comment on the original post [www.patrickkeisler.com, opens in a new window]

Loading comments...