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:

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

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.

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

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:

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

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.

Advertisements