Two Examples of Recursion with T-SQL

T-SQL is very good for dealing with set-based data, but it doesn’t shy away from recursion either. You can use either user defined functions (UDF) or common table expression (CTE) to achieve this and I put up hereunder two examples to solve the claissic problem of finding the organization levels of employees and their managers.

I use well-known names from the NBA franchise Miami Heat to provide some raw data:

[sourcecode language=”sql”]
CREATE TABLE Employees
(
EmployeeID INT ,
EmployeeName VARCHAR(50),
ReportsTo INT
)
Go

INSERT Employees VALUES
(1,’Micky Arison’, NULL),
(2, ‘Pat Riley’, 1),
(3, ‘Erik Spoelstra’, 2),
(4, ‘Dwyane Wade’, 3),
(5, ‘Lebron James’, 3),
(6, ‘Chris Bosh’, 4),
(7, ‘Ray Allen’, 5),
(8, ‘Shane Battier’, 4),
(9, ‘Norris Cole’, 8),
(10, ‘Michael Beaseley’, 8),
(11, ‘Mario Chalmers’, 8)
GO
[/sourcecode]

And by running a simple T-SQL statement SELECT * FROM Employees ORDER BY EmployeeID, you shall have the following resultset:

And the hierachy of this “organization” shall be something like this:

To recursively get an employee’s level in the organization, I created the below functions. The first one is for retrieving EmployeeID by EmployeeName and the second one implements the recursion logic of searching upwards in the organization tree.

[sourcecode language=”sql”]
CREATE FUNCTION dbo.fn_GetEmployeeID(@EmployeeName AS VARCHAR(50))
RETURNS INT
AS
BEGIN
DECLARE @EmployeeID AS INT
–Assuming EmployeeName is unique
SELECT @EmployeeID = EmployeeID FROM Employees
WHERE EmployeeName = @EmployeeName
RETURN @EmployeeID
END
GO
CREATE FUNCTION dbo.fn_GetLevel(@EmployeeID AS INT, @Level AS INT = 0)
RETURNS INT
AS
BEGIN
DECLARE @ReportsTo AS INT
SELECT @ReportsTo = ReportsTo FROM Employees
WHERE EmployeeID = @EmployeeID
IF @ReportsTo IS NOT NULL
RETURN dbo.fn_GetLevel(@ReportsTo, @Level+1)
RETURN @Level+1
END
GO
[/sourcecode]

With data populated and functions created, now you can use T-SQL statement SELECT dbo.fn_GetLevel(dbo.fn_GetEmployeeID('Lebron James'), DEFAULT) to retrieve the all-star’s level at Miami Heat and aslo statement
SELECT EmployeeID, EmployeeName, dbo.fn_GetLevel(EmployeeID) AS [EmployeeLevel] FROM Employees to get the below full table with every empoyee’s level info:

Another quick way to realize the recursion logic is to use CTE (common table expression) which can reference from the top node of the organization tree with the rest of the rows by using key words UNION ALL:
[sourcecode language=”sql”]
WITH tmp (EmployeeID, EmployeeName, EmployeeLevel) AS
(
SELECT E.EmployeeID, E.EmployeeName, 1 AS EmployeeLevel FROM Employees E
WHERE ReportsTo IS NULL

UNION ALL

SELECT E.EmployeeID, E.EmployeeName, T.EmployeeLevel + 1 AS EmployeeLevel
FROM Employees E INNER JOIN tmp T on E.ReportsTo = T.EmployeeID
)
SELECT * FROM tmp
[/sourcecode]
And the final output is exactly same as shown by the prior approach that uses UDF. Hopefully this will be helpful for some new learners on T-SQL.