SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Everybody Reports to Somebody

By Craig Hatley,

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 2 

Figure 3
 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 
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
        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
                Insert Into #Result (UserID)
                Select UserID From #Out
        Delete From #In
        Insert Into #In
        Select UserID From #Out
        Delete From #Out

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
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 . . .
Total article views: 5699 | Views in the last 30 days: 5
Related Articles

Selecting from hierarchies like Managers and Employees

Chuck Hoffman shows a technique using sets for selecting records from hierarchies such as Manager / ...


select [Employee_ID] = EmpID from dbo.Employee GROUP BY [Employee_ID]

select [Employee_ID] = EmpID from dbo.Employee group by [Employee_ID] --Group by error


Employee Retention From the Managers Perspective

Part 3 of Steve Jones' series on employee retention, this one focused on the manager's point of view...


Report Manager setting missing

Report Manager setting missing


user!userID used to build report parameter selection list

  When trying to user user_user!id to limit the input of report paramter's default values I get the...

advanced querying