Common Table Expressions

Common Table Expressions – Terminating the Previous Statement

Day 4 of Common Table Expression Month (June) at SteveStedman.com, today I will cover the topic of terminating the previous T-SQL statement.

These queries will be using that database that was set up in a previous posting on the CTE_DEMO Sample Database, if you haven’t set up the sample database, download it and set it up now.

To start with having come from a background years ago of writing C programs, the concept of terminating statements is natural to me. I always feel a bit uncomfortable of a SQL Statement isn’t properly terminated with a semicolon. If I am doing a T-SQL code review for someone else, I usually will make at least one comment about terminating T-SQL statements, unless it has already been done. I know that is not a requirement to terminate every SQL Statement, but it does make it easier to read. However in most cases, other than readability the termination of the previous statement really doesn’t matter. . . In most cases.

For instance, the following two sets of queries work exactly the same with our without the semicolons.

Batch 1:


SELECT * FROM Departments
SELECT * FROM (SELECT id, department, parent FROM Departments) as Dept

Batch 2:


SELECT * FROM Departments;
SELECT * FROM (SELECT id, department, parent FROM Departments) as Dept;

In most cases, when writing multiple T-SQL statements in a batch, the semicolon to terminate the lines really doesn’t matter, but there are a few cases in SQL Server where it does matter, and common table expressions are one of them.

Improper Termination

To start with lets take a look at the sample code from our previous posting titled “Writing Your First Common Table Expression with SQL Server“. If we tried to run this SQL without any semicolons, it would run fine if it was run one line at a time, but if we run it as a batch, it throws an error.

USE cte_demo
GO

SELECT * FROM Departments

WITH departmentsCTE(id, department, parent) AS
(
 SELECT id, department, parent
 FROM Departments
)
SELECT *
 FROM departmentsCTE

When run produces the following results:

Msg 336, Level 15, State 1, Line 5
Incorrect syntax near 'departmentsCTE'. If this is intended to be a 
common table expression, you need to explicitly terminate the previous 
statement with a semi-colon.

Which is a descriptive enough message stating that we need to explicitly terminate the previous statement with a semi-colon.

Proper Termination

When there is another T-SQL Statement in a batch prior to a CTE, that statement must be terminated with a semi-colon like this:

USE cte_demo
GO

SELECT * FROM Departments;

WITH departmentsCTE(id, department, parent) AS
(
 SELECT id, department, parent
 FROM Departments
)
SELECT *
 FROM departmentsCTE

Note, the only required semi-colon is the one at the end of the SELECT * FROM Departments; query, and we get the desired results.

GO Keyword in SSMS

When working in SQL Server Management Studio, an alternative is to terminate the batch, and the GO keyword is used to break a number of T-SQL statements into different batches, like this:


USE cte_demo;
GO

SELECT * FROM Departments

GO

WITH departmentsCTE(id, department, parent) AS
(
 SELECT id, department, parent
 FROM Departments
)
SELECT *
 FROM departmentsCTE

GO

Clean Code

Being an advocate for clean code, and clearing specifying your intentions, the way that I would write is with a semi-colon at the end of every T-SQL statement, and an extra semi-colon at the beginning of any CTE just to be safe.


USE cte_demo;
GO

SELECT * FROM Departments;

;WITH departmentsCTE(id, department, parent) AS
(
 SELECT id, department, parent
 FROM Departments
)
SELECT *
 FROM departmentsCTE;

How you decide to do it is up to your own coding standards, and as long as it is clearly defined and supports clean code it is just fine.