Getting All Employees - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Getting All Employees

Author

Topic

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-02-04 : 16:10:18

I have the following query where I'm trying to get all employees under part working under a particular person. For example,

Supervisor Employee
Al Smith John Johnson
John Johnson Mary Smith
John Johnson Jeff Johns
John Johnson Jim Williams
Jim Williams Al Jackson
Jim Williams Jane Doe

If someone selects Jim Williams as the supervisor, Al and Jane will appear under Jim as employees.If someoen selects John Johnson as the employee, all employees that directly report to John Johnson appear as well as teh reports for Jim Williams.

I create a temp table and assign a level of 0 to the supervisor, 1 to the next level and continue to increment until all users appear. I believe the script is getting stuck in the while loop.

Any suggestions?

CREATE procedure DBSP_SUP_LIST

/*************************************************
** Declare Arguments **
*************************************************/

@pSUP_NAME varchar(128) = NULL,
@pDEBUG int = NULL

AS

/*************************************************
** Declare Local Variables **
*************************************************/

/*************************************************
** Begin Procedure Code **
*************************************************/

/***************************
*** CREATE #CERT_DB_SUP ***
***************************/
CREATE TABLE #CERT_DB_SUP
(
SUP_NAME varchar(128) NOT NULL,
EMP_NAME varchar(128) NOT NULL,
SUP_LEVEL int NOT NULL
)

/***********************
*** CURSOR VARIABLES ***
***********************/

/*********************
*** SAVE VARIABLES ***
*********************/

/**********************
*** LOCAL VARIABLES ***
**********************/
DECLARE @lnRetCd int
DECLARE @lszDISPLAY varchar(128)
DECLARE @lnRowCount int
DECLARE @lnLevel int

/*********************
*** INITIALIZATION ***
*********************/
SELECT @lnRetCd = 0
SELECT @lszDISPLAY = ''
SELECT @lnRowCount = -1
SELECT @lnLevel = 1

/**************************************************
*** POPULATE THE TEMP TABLE WITH DIRECT REPORTS ***
**************************************************/
INSERT INTO
#CERT_DB_SUP
SELECT
supervisor_name,
employee_name,
0
FROM
EMPLOYEES
WHERE
employee_name = @pSUP_NAME

/**************************************************
*** POPULATE THE TEMP TABLE WITH DIRECT REPORTS ***
**************************************************/
INSERT INTO
#CERT_DB_SUP
SELECT
supervisor_name,
employee_name,
1
FROM
EMPLOYEES
WHERE
supervisor_name = @pSUP_NAME

/************
*** DEBUG ***
************/
IF @pDEBUG = 1
BEGIN
PRINT 'Direct Reports from #CERT_DB_SUP'

SELECT
*
FROM
#CERT_DB_SUP
END

WHILE (@lnRowCount <> 0)
BEGIN
INSERT INTO
#CERT_DB_SUP
SELECT
DB.supervisor_name,
DB.employee_name,
@lnLevel + 1
FROM
EMPLOYEES DB,
#CERT_DB_SUP TMP
WHERE
DB.employee_name = TMP.EMP_NAME AND
TMP.SUP_LEVEL = @lnLevel

SELECT @lnRowCount = @@ROWCOUNT

SELECT @lnLevel = @lnLevel + 1
END

/**********************
*** DISPLAY RESULTS ***
**********************/
SELECT
*
FROM
#CERT_DB_SUP

/************************************
*** TRUNCATE AND DROP TEMP TABLES ***
************************************/
TRUNCATE TABLE #CERT_DB_SUP
DROP TABLE #CERT_DB_SUP

RETURN @lnRetCd
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-04 : 16:27:01

Are you using SQL Server 2005? Have a look at recursive CTE.

E 12°55'05.63"
N 56°04'39.26"

bielen
Yak Posting Veteran

97 Posts

Posted - 2009-02-04 : 17:05:43

I'll check it out, but I'm currently on SQL 2000. Any other options?

quote:
Originally posted by Peso

Are you using SQL Server 2005? Have a look at recursive CTE.

E 12°55'05.63"
N 56°04'39.26"

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-04 : 17:22:28

Yes Recursive CTE will solve your problem.
Something like this.

WITH Emp_CTE AS (
SELECT Employee, Supervisor
FROM Table1
UNION ALL
SELECT e.Employee
FROM Table1 T
INNER JOIN Emp_CTE T1 ON T1.Employee = T.Supervisor
)
SELECT *
FROM Emp_CTE

Am not sure of this either..just typed whatever i read.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-04 : 17:28:34

quote:
Originally posted by vijayisonly

Yes Recursive CTE will solve your problem.
Something like this.

WITH Emp_CTE AS (
SELECT Employee, Supervisor
FROM Table1
UNION ALL
SELECT e.Employee
FROM Table1 T
INNER JOIN Emp_CTE T1 ON T1.Employee = T.Supervisor
)
SELECT *
FROM Emp_CTE

Am not sure of this either..just typed whatever i read.

That will not work on SQL Server 2000.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-04 : 17:42:22

See this:

http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources