find.....underlings.....
searching......
underlings found......

I recently was given the task to create a stored procedure to find people who are under other people from our users database.

The requester requested that they would put in the user's ID and it would return everyone under them...and everyone under those people...and so on.

The table structure looked something like this:

CREATE TABLE [dbo].[tblUser](
[UserID] [int] NOT NULL,
[EmployeeNum] [char](7) NULL,
[FirstName] [varchar](20) NULL,
[LastName] [varchar](20) NULL,
[StatusID] [int] NULL,
[ManagerEmployeeNum] [varchar](20) NULL
 CONSTRAINT [PK_tblUser] PRIMARY KEY CLUSTERED 
(
[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

So to complete this task I created a temp table, some counters, and a little while loop.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Adam Mikolaj
-- Create date: 23 June 2011
-- Description:SP to find what people are under what people (mangers)
-- =============================================
CREATE PROCEDURE uSP_FindUnderlings
@UserID int
AS
BEGIN
SET NOCOUNT ON;

create table #peopleunder(
employeenum varchar(20))
declare 
@employeeNum varchar(20),
@countA int = 1, @countB int = 0
set @employeeNum = (select rtrim(EmployeeNum) from tblUser where UID = @UserID)
insert into #peopleunder
select @employeeNum
while @countA <> @countB
begin 
set @countA = (select COUNT(*) from #peopleunder)

                insert into #peopleunder
select rtrim(EmployeeNum) as employeenum from tblUser where StatusID = 1and (ManagerEmployeeNum in (select EmployeeNum from #peopleunder) and EmployeeNum not in (select EmployeeNum from #peopleunder)) 

set @countB = (select COUNT(*) from #peopleunder)

end 
select * from tblUser where EmployeeNum in (select EmployeeNum from #peopleunder) and UID <> @Uid and StatusID = 1
drop table #peopleunder
END
GO

The basic logic behind this is that this loop will continue to insert records into the temp table until it doesn't find any more records. I use the counters to calculate the pre-insert and post-insert. If they match, the loop is finished.

Just a simple little SP to start my morning.