SQL Server Common Table Expressions (CTEs)
Common Table Expressions
Using CTEs in Queries and Stored Procedures in code.
Getting Started with SQL CTE Code
Common Table Expressions were introduced with SQL Server 2005. They are an alternative to using temp tables to perform operations such as recursion and aggregating data.
Sample syntax for a CTE:
WITH myCTE
AS
(SELECT * FROM dbo.vw_employees)
select * from myCTE
CTE using Aggregate Data
Another example of a CTE used to aggregate data and then update a temp table. This code shows the wrong way to try to update the temp table using an aggregate in the query.
CREATE TABLE #tmp (
SalesPersonID int null,
FullName varchar(255) Null,
SalesStatus varchar(255) null,
TotalSales money null
)
INSERT INTO #tmp (SalesPersonID, FullName)
SELECT SalesPersonID, FirstName + ' ' + LastName
FROM Sales.SalesPerson s INNER JOIN HumanResources.Employee e
ON e.EmployeeID = s.SalesPersonID
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
UPDATE #tmp
SET TotalSales = SUM(TotalDue)
FROM #tmp INNER JOIN Sales.SalesOrderHeader ON
#tmp.SalesPersonID = Sales.SalesOrderHeader.SalesPersonID
Instead of another temp table to pull in the aggregate information for the update, a CTE can be substituted to get the data.
WITH cteSalesOrderHeader (SalesPersonID, cTotalSales)
AS
(
SELECT SalesPersonID, SUM(TotalDue)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
)
UPDATE #tmp
SET TotalSales = cTotalSales
FROM #tmp
INNER JOIN cteSalesOrderHeader ON
#tmp.SalesPersonID = cteSalesOrderHeader.SalesPersonID
SELECT * FROM #tmp
SQL CTE using Recursion
Example of recursion in pulling in the various levels of a Bill of Materials from the AdventureWorks Database. This one uses ProductID 794 to get the components.
WITH BOM_cte(ProductAssemblyID, ComponentID, ComponentDesc, BOMLevel, RecursionLevel)
AS (SELECT b.ProductAssemblyID, b.ComponentID, p.Name, b.BOMLevel, 0
FROM Production.BillOfMaterials b INNER JOIN Production.Product p
ON b.ComponentID = p.ProductID
WHERE b.ProductAssemblyID = 794
UNION ALL
SELECT b.ProductAssemblyID, b.ComponentID, p.Name, b.BOMLevel, RecursionLevel + 1
FROM BOM_cte cte
INNER JOIN Production.BillOfMaterials b
ON b.ProductAssemblyID = cte.ComponentID
INNER JOIN Production.Product p
ON b.ComponentID = p.ProductID
)
SELECT b.ProductAssemblyID, b.ComponentID, b.ComponentDesc, b.BOMLevel, b.RecursionLevel
FROM BOM_cte b
GROUP BY b.ComponentID, b.ComponentDesc, b.ProductAssemblyID, b.BOMLevel, b.RecursionLevel
ORDER BY b.BOMLevel, b.ProductAssemblyID, b.ComponentID
OPTION (MAXRECURSION 25)
