Hi,

   I have received a private question asking about how to deal with employees that may report to multiple bosses and I wanted to provide my thoughts on the subject.  I think the best approach to address this issue would be to move the 'Manager User ID' field to a separate table containing at least the 'User ID' and 'Manager User ID' columns which could be joined to the 'Employee' table via the 'User ID' column.  You could then modify the stored procedure to include the new table via a join such as (Changes Highlighted In Yellow) . . .

CREATE PROCEDURE select_subordinates_by_manager

@user-id 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 Distinct UserID From Test..Employee as e

Join Test..ManagedBy as m on m.UserID = e.UserID

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