Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


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

Simple CTE

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

error Message

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)

Bill Of Material