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