How to use WITH clause CTE and recursive CTE

The WITH clause CTE is used to increase performance when the same SQL is used repeatedly.
It has the same effect as creating a table without creating the table, but is actually stored in a temporary table.

WITH clause CTE, recursive CTE features

• A temporarily named set of DATA, also known as a Common Table Expression (CTE).
• The CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all CTE columns.
• CTE has the same performance as temporary tables.
• A CTE cannot have more than one WITH clause.
• INTO, OPTION clause with query hint, and OR BROWSE cannot be used in CTE.
• A recursive CTE definition must contain at least two CTE query definitions: one anchor member and one recursive member.

WITH clause basic structure

CTE basic structure.

-- syntax
WITH CTE_TABLE_NAME(COL1, COL2, COL3...)
AS
(
    <SQL statement>
)
SELECT COL1, COL2, COL3 ... 
FROM CTE_TABLE_NAME;
 
--  WITH clause basic structure.
WITH baseCte(MngId, EmpID, Title, cteParam ) AS
(
  SELECT MngId, EmpID, Title , 'paramTest'  paramTest
    FROM dbo.UserInfoForCte  
)
SELECT MngId, EmpID, Title ,cteParam
FROM baseCte
ORDER BY MngId;

How to use multiple CTE definitions in a single query.

• You can use commas to separate CTE query definitions.

--Using multiple CTEs in a statement separated by commas
WITH
cteFirst (Mngcnt, dept) AS
(
  SELECT COUNT(*) Mngcnt, 'Manager' dept  from UserInfoForCte where MngId = '273'
),
cteSecond (saleCnt, dept) AS
(
  SELECT COUNT(*) saleCnt, 'Sales' dept  FROM UserInfoForCte where MngId > '273'
)
SELECT Mngcnt, dept FROM cteFirst
UNION ALL
SELECT  saleCnt, dept FROM cteSecond;

Utilizing WITH clause CTE

Recursive CTE structure.

WITH CTE_TABLE_NAME(COL1, COL2, COL3 ...)
AS
(
    <query statement1 : SELECT * FROM TABLE_A>
    UNION ALL
    <query statement2 : SELECT * FROM TABLE_A JOIN CTE_TABLE_NAME>
)
SELECT * FROM CTE_TABLE_NAME;

How to display a hierarchical list using a recursive common table expression.

• When a company has a hierarchical structure with a hierarchical representation of presidents, vice presidents, and managers, it is usually necessary to use recursive CTEs.
• Source

-- Display hierarchical lists using recursive common table expressions
WITH DirectChart(MngId,Name, Title, EmpID, EmployeeLevel, Sort)
AS (
    SELECT MngId,CONVERT(VARCHAR(255), e.FirstName + ' ' + e.LastName),
          e.Title,
          e.EmpID,
          1,
          CONVERT(VARCHAR(255), e.FirstName + ' ' + e.LastName)
     FROM dbo.UserInfoForCte AS e
    WHERE e.MngId IS NULL
    UNION ALL
    SELECT e.MngId ,CONVERT(VARCHAR(255), REPLICATE ('|   ' , EmployeeLevel) + e.FirstName + ' ' + e.LastName),
          e.Title,
          e.EmpID,
          EmployeeLevel + 1,
          CONVERT (VARCHAR(255), RTRIM(Sort) + '|   ' + FirstName + ' ' +  LastName)
    FROM dbo.UserInfoForCte AS e
    JOIN DirectChart AS d ON e.MngId = d.EmpID
  )
SELECT MngId, EmpID, Name, Title, EmployeeLevel,Sort
FROM DirectChart
ORDER BY Sort;

HOW TO USE MAXRECURSION.

• Use MAXRECURSION to prevent recursive CTEs from entering infinite loops.
• The method is to add OPTION (MAXRECURSION number) at the end of the query statement.

-- How to limit recursive calls using MAXRECURSION
WITH DirectChart(MngId, EmpID, Title, EmployeeLevel) AS
(
   SELECT MngId, EmpID, Title, 1 AS EmployeeLevel
     FROM dbo.UserInfoForCte
    WHERE MngId IS NULL
   UNION ALL
   SELECT e.MngId, e.EmpID, e.Title, EmployeeLevel + 1
     FROM dbo.UserInfoForCte AS e
     INNER JOIN DirectChart AS d
     ON e.MngId = d.EmpID
)
SELECT MngId, EmpID, Title, EmployeeLevel
FROM DirectChart
OPTION (MAXRECURSION 10); --- Just enter the number of recursive calls.

Input data for recursive query practice

USE sampleDB;
DROP TABLE IF EXISTS UserInfoForCte;

 CREATE TABLE dbo.UserInfoForCte
(
    EmpID SMALLINT NOT NULL,
    FirstName NVARCHAR(30) NOT NULL,
    LastName NVARCHAR(40) NOT NULL,
    Title NVARCHAR(50) NOT NULL,
    DeptID SMALLINT NOT NULL,
    MngId SMALLINT NULL,
    CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmpID ASC),
    CONSTRAINT FK_MyEmployees_MngId_EmpID 
    FOREIGN KEY (MngId) 
    REFERENCES dbo.UserInfoForCte (EmpID)
);
-- Populate the table with values.
INSERT INTO dbo.UserInfoForCte VALUES
     (1, N'Ken', N'Sanchez', N'Chief Executive Officer',16, NULL)
    ,(273, N'Brian', N'Welcker', N'Vice President', 3, 1)
    ,(274, N'Stephen', N'Jiang', N'North American Sales Manager', 3, 273)
    ,(275, N'Michael', N'Blythe', N'Sales ', 3, 274)
    ,(276, N'Linda', N'Mitchell', N'Sales ', 3, 274)
    ,(285, N'Syed', N'Abbas', N'Pacific Sales Manager', 3, 273)
    ,(286, N'Lynn', N'Tsoflias', N'Sales ', 3, 285)
    ,(16, N'David', N'Bradley', N'Marketing Manager', 4, 273)
    ,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);