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 . . .
