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
to get the below full table with every empoyee’s level info:
SELECT EmployeeID, EmployeeName, dbo.fn_GetLevel(EmployeeID) AS [EmployeeLevel] FROM Employees
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.