Have you ever wondered about the logistics of the organizational structure of a company? How many layers of management exist from the top to the bottom? These questions became relevant to me when I had to support a new application that required managers to access data associated with their subordinate employees, regardless of how many layers below they were in the organizational structure.

Let’s consider an example. If we have an employee named Bob, and my function receives Bob’s User ID, it should return all the people who report to Bob, directly or indirectly. This means that if Sue reports to Todd, and Todd reports to Bob, then both Sue and Todd should be included in the result.

To achieve this, we can use a table that stores employee data, which includes a ‘User ID’ field (the primary key) and a ‘Manager User ID’ field that contains the User ID of the manager each employee directly reports to. This follows the “everybody reports to somebody” philosophy.

Now, the challenge is to write a stored procedure that can handle any organizational structure without assuming any foreknowledge of the number of management layers. Recursion seems like the obvious solution, but SQL recursion has limitations, such as a maximum of 32 loops and restricted data types for passing data back up through the recursive loop chain.

After much consideration, I came up with the following stored procedure:

CREATE PROCEDURE select_subordinates_by_manager
     @UserID int 
AS
SET NOCOUNT ON
DECLARE @UserCount int
SET @UserCount = 1
CREATE TABLE #In (UserID int) 
CREATE TABLE #Out (UserID int) 
CREATE TABLE #Result (UserID int) 
INSERT INTO #In (UserID) VALUES (@UserID)
INSERT INTO #Result (UserID) VALUES (@UserID)
WHILE @UserCount > 0
    BEGIN
        INSERT INTO #Out (UserID)
        SELECT UserID FROM Employee 
        WHERE ManagerUserID IN (SELECT UserID FROM #In)
        SELECT @UserCount = (SELECT COUNT(UserID) FROM #Out)
        IF @UserCount > 0
            BEGIN
                INSERT INTO #Result (UserID)
                SELECT UserID FROM #Out
            END
        DELETE FROM #In
        INSERT INTO #In
        SELECT UserID FROM #Out
        DELETE FROM #Out
    END
SET NOCOUNT OFF
SELECT UserID FROM #Result

This stored procedure starts by returning a list of User IDs that report directly to the specified manager User ID. It then runs in a loop, checking for User IDs that report to the list of User IDs returned in the previous loop until no more User IDs are found. The User IDs returned from each successive loop are added to the temporary table called #Result, which is ultimately used to return a list of User ID values representing the subordinate employees.

To retrieve the names of the subordinate employees, we can use another stored procedure:

CREATE PROCEDURE select_subordinate_names_by_manager 
    @UserID int
AS
CREATE TABLE #Subordinates (UserID int)
INSERT INTO #Subordinates (UserID)
EXEC ('dbo.select_subordinates_by_manager ' + @UserID)
SELECT e.UserID, e.FirstName, e.LastName 
FROM Employee AS e
JOIN #Subordinates AS s ON s.UserID = e.UserID
ORDER BY e.FirstName, e.LastName

Now, you might be concerned about the performance of this solution, especially with the extensive use of temporary tables. However, in a test environment with a single organizational structure branch representing 50 layers of management and over 4500 employees, the overall execution time was only 64 ms. This is quite impressive considering the complexity of the task.

Understanding the organizational structure of a company is crucial for many applications, and SQL Server provides powerful tools to handle such scenarios. By leveraging recursion and temporary tables, we can efficiently retrieve data for managers and their subordinate employees, regardless of the number of management layers.

I hope you found this article informative and interesting. Feel free to leave any comments or questions below!