Everybody Reports To Somebody

Have you ever thought about the logistics of the organizational structure of a company? Do you

know how many layers of management exist from the top to the bottom? I was recently forced to consider

these very questions to support a new application that required managers to be able to access data

associated with any subordinate employee. This could be an employee that directly reports to the manager

or an employee that is several layers below the manager in the overall organizational structure.

For example, if my function received Bob's User ID, it should return the following people

based on the sample organizational chart (See Figure 1) . . .

• Bob
• Sue
• Todd
• Mary
• Sandy
• Megan
• Randy

 Figure 1 

The table that stores our employee data (see figure 2) includes a 'User ID' field which

is the primary key for the table and a 'Manager User ID' field which contains the User ID of the manager

that each employee directly reports to (everybody reports to somebody philosophy). If Bob has a

User ID of '2' in our sample above, Sue, Todd, and Mary would all have a value of '2' in their

'Manager User ID' field indicating that they report directly to Bob (see figure 3).

 Figure 2 

 Figure 3 

Now for the tricky part . . . One of the objectives of our stored procedure is that it should not

assume any foreknowledge of the organizational structure including the number of management layers.

As I started this project I knew that my code would be required to perform some type of recursion,

so I found a great article on the Microsoft web site that discussed recursion in SQL. After spending a

little time reading the article, I discovered a couple of potential significant limitations in that SQL

recursion is limited to 32 loops (to prevent infinite loop conditions) and most of the examples for

passing data back up through the recursive loop chain used a data type that was restricted by a maximum

length. Although our company doesn't currently have 32 layers of management hierarchy, I simply didn't

want to settle for a 'work around' solution because I suspected it would eventually be an issue for us.

This caused me to start looking for alternatives which after much prayerful consideration yielded 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 Test..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

The stored procedure basically starts off by returning a list of the 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 that were 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.

We typically use the User ID list returned from the previous stored procedure in conjunction with another stored procedure (see below) to return a list representing the employees that report to the specified manager either directly or indirectly.

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 Test..Employee as e
  Join #Subordinates as s on s.UserID = e.UserID
  Order By e.FirstName, e.LastName

After looking at the code and the extensive use of temporary tables you may be concerned about how well

this solution will perform. As a baseline, I measured the overall execution time of this technique in a

test environment (see environment details below) with a single organizational structure branch that

represents 50 layers of management and more than 4500 employees. Needless to say I was pleasantly

surprised to discover that the overall execution time was only 64 ms.

Test Environment

• SQL Platform : Microsoft SQL Server 2000 (Developer Edition)
• Hardware Platform : Workstation Class PC (Single Pentium 4 3.2Ghz/3GB RAM)

I hope you found this article informative and interesting . . .