Find…underlings…

Find…..underlings…..
searching……
underlings found……

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

The requester wanted to put in the user’s ID and have it return everyone under them…and everyone under those people…and so on.

The table structure looked something like this:

<code>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] </code>

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 &lt;&gt; @countB
	BEGIN
		SET @countA = (SELECT COUNT(*) FROM #peopleunder)
 
                INSERT INTO #peopleunder
		SELECT rtrim(EmployeeNum) AS employeenum FROM tblUser WHERE StatusID = 1
AND (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 &lt;&gt; @Uid AND StatusID = 1
DROP TABLE #peopleunder
END
GO

 

The basic logic behind this is using the loop to continue to insert records into the temp table until it doesn’t find anymore 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.