Recursive CTE in TSQL

A Common Table Expression(CTE) is a temporary resultset within the scope of the immediate SELECT, INSERT, DELETE, UPDATE and CREATE VIEW statements. It is different from derived table as its scope is only persisted for immediate query and it can be self referencing.

In this small tutorial we are going to see how we can use a recursive CTE to perform query on hierarchical data. Recursive CTE is a CTE in which a query runs repeatedly and evaluates the results. A recursive CTE does have following elements.

1. Anchor query, which only executes one time and these results act like input to recursive query.
2. Union All, used to combine all results from Anchor query and Recursive Query.
3. Recursive Query, this query repeats itself for multiple times.
4. Inner Join, with the present CTE to connect recursive set results.

In the following example we will query the results of a employee and his direct and indirect boss. We will be using MAXRECURSION option to specify CTE to terminate after MAXRECURSION number by throwing an exception. This prevents CTE going into infinite loop. MAXRECURSION value needs to be intermediate value, too short value might not yield correct results.

Code is self explanatory and comments are provided for ease of understanding.

/************************* Create TestDB Database **************************/ 
 
IF  NOT EXISTS (SELECT [name] FROM master.[dbo].[sysdatabases] WHERE [name] = 'TestDB')
BEGIN
       CREATE DATABASE [TestDB];
END
GO
 
USE [TestDB]
GO
 
/************************* Create Employee Table ****************************/
 
IF EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'U' AND name = 'Employees')
BEGIN
    DROP TABLE [dbo].[Employees];
END
GO
 
CREATE TABLE [dbo].[Employees](
    [EmpId] [int] IDENTITY(1,1) NOT NULL,
    [EmpName] [nvarchar](32) NOT NULL,
    [EmpManagerId] [int] NULL
)
GO
 
 
/************************* Enter Employee Details **************************/
 
INSERT INTO [dbo].[Employees] ([EmpName],[EmpManagerId]) VALUES ('R1',NULL);
INSERT INTO [dbo].[Employees] ([EmpName],[EmpManagerId]) VALUES ('R2',1);
INSERT INTO [dbo].[Employees] ([EmpName],[EmpManagerId]) VALUES ('R3',1);
INSERT INTO [dbo].[Employees] ([EmpName],[EmpManagerId]) VALUES ('R4',2);
INSERT INTO [dbo].[Employees] ([EmpName],[EmpManagerId]) VALUES ('Y1',2);
INSERT INTO [dbo].[Employees] ([EmpName],[EmpManagerId]) VALUES ('Y2',3);
INSERT INTO [dbo].[Employees] ([EmpName],[EmpManagerId]) VALUES ('Y3',4);
INSERT INTO [dbo].[Employees] ([EmpName],[EmpManagerId]) VALUES ('Y4',3);
INSERT INTO [dbo].[Employees] ([EmpName],[EmpManagerId]) VALUES ('T1',5);
INSERT INTO [dbo].[Employees] ([EmpName],[EmpManagerId]) VALUES ('T2',6);
INSERT INTO [dbo].[Employees] ([EmpName],[EmpManagerId]) VALUES ('T3',3);
INSERT INTO [dbo].[Employees] ([EmpName],[EmpManagerId]) VALUES ('T4',4);
GO
 
 
/******************* Update Employees who are only Devs *********************
Get Hirarchy data along with direct and indirect reports. Here we use Inner 
Join because we dont have 'Boss' and 'BossToBoss' in the Employees Table. In
the first query we get the overall boss for all employees, so that is the reason
we use 'NO' in both Boss and BossToBoss fields as he doesn't have one. Then
we use this anchor results and from there we traverse the complete hirerchy
using recursive query and union all.
****************************************************************************/
WITH EmpHirerchyDate AS (
 
 
       -- Anchor Query
       SELECT 
              [EmpName] AS Name,
              CAST('No' AS NVARCHAR) AS Boss,
              CAST('No' AS NVARCHAR) AS BossToBoss,
              [EmpId]
       FROM
              [dbo].[Employees]
       WHERE
              [EmpManagerId] IS NULL
 
 
       -- Union
       UNION ALL
 
 
       -- Recursive Query
       SELECT 
              E.[EmpName] AS Name,
              CAST(H.[Name] AS NVARCHAR) AS Boss,
              CAST(H.[Boss] AS NVARCHAR) AS BossToBoss,
              E.[EmpId]
       FROM
                     [dbo].[Employees] E 
              INNER JOIN
                     [EmpHirerchyDate] H
              ON
                     E.[EmpManagerId] = H.[EmpId]
              WHERE
                     [EmpManagerId] IS NOT NULL
)
SELECT 
       [Name],
       [Boss],
       [BossToBoss]
FROM 
        [EmpHirerchyDate] 
ORDER BY 
        [EmpId] OPTION (MAXRECURSION 5);
 
 
 
/************************** Drop TestDB Database ***************************/
USE master
GO
 
IF EXISTS (SELECT [name] FROM master.[dbo].[sysdatabases] WHERE [name] = 'TestDB')
BEGIN
       DROP DATABASE [TestDB];
END
GO

Output :