How does Recursive CTE works in Sql Server?

In the session, you'll learn about recursive common table expressions. Before continuing with this post, I strongly recommend you to read the article about CTE provided below.

• What is CTE in sql server with example
• How to update table using CTE in sql server

You have the TblEmployeeManager table, which includes columns such as EmpId, EmployeeName, and Manager_Id.

Since a manager is also an employee in the organization, we store both employee and manager details in the same table. Now, if I ask you who is the manager of Michael, you will look up the Manager_Id column, which is number 5.

You will then take that value and look it up in the EmpId column. Number 5 corresponds to Grayson. So Michael’s manager is Grayson.

In this case, we are referencing the Manager_Id column and looking up another column in the same table. Hence, it’s called a self-referencing table.

Now, if I ask you to display employee names along with their manager’s names, then we have to join this table with itself. And as we know, joining a table with itself is called a self join.

We have talk about self join extensively in What is SELF JOIN ? with Realtime Example post.

So, this is a simple self-join query. Obviously, if I just want to display the employee names along with their manager names, we can use a simple self join.

Select Emp.EmployeeName as [Employee Name],
IsNull(Mng.EmployeeName, 'Super Boss') as [Manager Name]
from TblEmployeeManager Emp
left join TblEmployeeManager Mng
on Emp.Manager_Id = Mng.EmpId

If you look at Anthony, Manager_Id is NULL, which means Anthony does not have any manager. Therefore, we are displaying 'Super Manager' with the help of the ISNULL function.

So if you simply want to display an employee's name along with their manager’s name, you can use a self-join.

Sql server hierarchical query example

But let’s say I want to display the employee name, their manager name, and along with that, I also want to display the level in the organization.

Now, if you look at the organization chart below, Anthony is the super manager because he doesn’t have a Manager_Id, which means he’s at the top of the hierarchy. Then, Lincoln and Ryan are the direct reports of Anthony.

If you look at Anthony, his employee ID is 2, and Lincoln and Ryan have Manager_Id 2. So obviously, Lincoln and Ryan report to Anthony. Additionally, Lincoln similarly has one direct report, as does Ryan. So, if you examine the hierarchy, Anthony is at the highest level, while Jackson, Michael, Luke, and Jacob are at the bottom of the hierarchy.

In terms of levels within the hierarchy, Anthony is at level one, being at the top. Lincoln and Ryan are at level two. Similarly, Gabriel and Grayson are at level three, while Jackson, Jacob, Michael, and Luke are at the bottom of the hierarchy with level four.

So right now, I want to display the employee name, their manager's name, and their level within the organization. To achieve this, we can use recursion.

Using recursive CTE the code will become a lot easier.

With
  EmployeesManagerCTE (EmpId, Name, ManagerId, [Level])
as
  (
    Select EmpId, EmployeeName, Manager_Id, 1
    from TblEmployeeManager
    where Manager_Id is null    
    union all   
    Select TblEmployeeManager.EmpId, TblEmployeeManager.EmployeeName, 
    TblEmployeeManager.Manager_Id, EmployeesManagerCTE.[Level] + 1
    from TblEmployeeManager
    join EmployeesManagerCTE
    on TblEmployeeManager.Manager_Id = EmployeesManagerCTE.EmpId
  )
Select EmpCTE.Name as Employee, Isnull(MgrCTE.Name, 'Super Boss') as Manager, 
EmpCTE.[Level] 
from EmployeesManagerCTE EmpCTE
left join EmployeesManagerCTE MgrCTE
on EmpCTE.ManagerId = MgrCTE.EmpId

We know that we create a CTE using the 'WITH' keyword. So, with EmployeesManagerCTE.

Here, EmployeesManagerCTE is a CTE with four columns: EmployeeId, Name, Manager_Id, and Level.

I've enclosed 'Level' in square brackets because 'level' is a keyword in SQL Server. If you want to use a keyword as a column name, you need to wrap it inside square brackets.

If you look at the CTE query within the 'AS' block, we are again referencing the CTE within the CTE. So, this kind of becomes a self-referencing CTE.

Table Script

/****** Object:  Table [dbo].[TblEmployeeManager]    Script Date: 09/22/2020 7:21:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblEmployeeManager](
    [EmpId] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeName] [nvarchar](max) NULL,
    [Manager_Id] [int] NULL,
 CONSTRAINT [PK_TblEmployeeManager] PRIMARY KEY CLUSTERED 
(
    [EmpId] ASC
)
)
GO
SET IDENTITY_INSERT [dbo].[TblEmployeeManager] ON 
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (1, 'Michael', 5)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (2, 'Jackson', 6)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (3, 'Jacob', 6)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (4, 'Luke', 5)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (5, 'Grayson', 8)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (6, 'Gabriel', 9)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (7, 'Anthony', NULL)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (8, 'Lincoln', 7)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (9, 'Ryan', 7)
GO
SET IDENTITY_INSERT [dbo].[TblEmployeeManager] OFF
GO