SQL Server - Common Table Expressions (CTE)

* Compatibility: SQL Server 2005 onwards
Common Table Expressions (CTE) construct was introduced with Microsoft SQL Server 2005. CTE behaves like a derived table but unlike derived table which has statement level scope CTE offers a procedure level.

CTE helps in writing more modular code, which helps in better readability and reducing complexity of query.

CTE provides following benefits:
. Ability to call block of query (CTE declaration) multiple times like a function call in modern
programming language
. Ability to provide recursion logic (with robust exit condition)
. Ability to use result set (CTE call) in multiple statements or single statement (self join)

CTE is described with the following syntax:

WITH MyCTE1 (OrderID, CustomerID) AS
(
SELECT
OrderID, CustomerID
FROM
MyTable1
)
, MyCTE2 (FirstName, LastName)
(
SELECT
FirstName, LastName
FROM
MyTable2
)
/*Multiple CTE’s can be declared with ‘,’*/

Where
. MyCTE1 is CTE Name used to reference CTE in queries
. Column list (optional), good practice to declare them to avoid confusion
. Query declaration after the AS keyword
. Multiple CTE’s can be declared putting ‘,’ after previous ending declaration

CTE can then be used as following (we will see detailed example later):
SELECT * FROM MyCTE

CTE can be declared in stored procedures, user-defined functions (UDF), triggers, or views.

For more details on CTE’s visit: MSDN

Let us consider a real-world CTE example using NorthWind database. You can download and install NorthWind database from the following location.

Consider this scenario; we want to run a report which gives us Order detail by OrderID, EmployeeID or CustomerID, our sample query will only return Employee name, Contact name, Company name, Phone, Fax and Total purchase price for the order (the objective is to make the stored procedure flexible enough to fetch data for all the input parameters or some).

If we follow conventional T-SQL approach we would have to write multiple T-SQL statements to achieve our objective, but as you can see with one stored procedure using combination of CTE, Dynamic tables and Coalesce statement we made the entire stored procedure multi-functional, we can use the stored procedure to run reports like Sales by Employee, Sales by Customer, Overall Sales total and many more cases depending on how you use the data, of course we can change the query criteria to make our stored procedure even more flexible or extendable.

Stored procedure code:

USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[GetCustomerOrders_sp] Script Date: 08/17/2008 21:37:16 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
/*
--all
EXEC dbo.GetCustomerOrders_sp null, null, null
--by OrderID
EXEC dbo.GetCustomerOrders_sp 10682, null, null
--by CustomerID
EXEC dbo.GetCustomerOrders_sp null, null, 'ANTON'
--by EmployeeID
EXEC dbo.GetCustomerOrders_sp null, 9, null
--exact match
EXEC dbo.GetCustomerOrders_sp 10263, 9, 'ERNSH'
*/
ALTER PROCEDURE [dbo].[GetCustomerOrders_sp]
(
@OrderID int = null,
@EmployeeID int = null,
@CustomerID nVarchar(5) = null
)
AS
BEGIN
--no extra columns
SET NOCOUNT ON;

/*CTE Declaration block */
WITH CustomerOrders (OrderID, CustomerID, EmployeeID, ContactName, CompanyName, Phone, Fax, TotalPurchasePrice) AS
(
SELECT OrderTotal.OrderID As OrderID, C.CustomerID As CustomerID, E.EmployeeID As EmployeeID,
C.ContactName, C.CompanyName, C.Phone, C.Fax, OrderTotal.TotalPurchasePrice
FROM
Customers C
INNER JOIN
(
SELECT ORD.OrderID As OrderID, O.CustomerID, O.EmployeeID,
Sum(CAST((UnitPrice * Quantity) * (1-Discount)/100 * 100 AS Money)) AS TotalPurchasePrice
FROM
[Order Details] ORD
INNER JOIN ORDERS O ON O.OrderID = ORD.OrderID
GROUP BY ORD.ORDERID, O.CustomerID, O.EmployeeID
) AS OrderTotal ON OrderTotal.CustomerID = C.CustomerID
INNER JOIN Employees E ON OrderTotal.EmployeeID = E.EmployeeID
),
CustomerDetail (CustomerID, ContactName, CompanyName, Phone, Fax) AS
(
SELECT CustomerID, ContactName, CompanyName, Phone, Fax
FROM Customers
)
/*Note Multiple CTE's can be declared in declaration block*/
--determine which result to extract
SELECT
OrderID, E.LastName + ', ' + E.FirstName AS EmployeeName, E.EmployeeID, ContactName, CompanyName, Phone, Fax, TotalPurchasePrice
FROM
Employees E
INNER JOIN CustomerOrders CO ON CO.EmployeeID = E.EmployeeID
WHERE
CO.EmployeeID LIKE Coalesce(@EmployeeID, CO.EmployeeID)
AND CO.OrderID LIKE Coalesce(@OrderID, CO.OrderID)
AND CO.CustomerID LIKE Coalesce(@CustomerID, CO.CustomerID)
ORDER BY E.EmployeeID ASC
END

Test Scripts result:

Note: Result sets images are adjusted to show the first few records and rest are omitted

--all
EXEC dbo.GetCustomerOrders_sp null, null, null

--by OrderID

EXEC dbo.GetCustomerOrders_sp 10682, null, null

--by CustomerID

EXEC dbo.GetCustomerOrders_sp null, null, 'ANTON'

--by EmployeeID

EXEC dbo.GetCustomerOrders_sp null, 9, null

--exact match

EXEC dbo.GetCustomerOrders_sp 10263, 9, 'ERNSH'