Solved

# Employee hierarchy tree

Posted on 2011-04-28
Medium Priority
730 Views
I have a table that has a StaffNumber Field and a Senior field along with some other data. The Senior field is a staff number that may belong to a manager or Head of department. I need to get everyone from the table where the Senior field appears but also if those people have other people reporting to them to also get that info out.

I would like to create a stored procedure for this.
If Alison is called it should give everyone's info in the given table example but if Jim is called it should give Jim Paul and Kents details if that makes sense

Table Example:
Table Name: StaffDetais

Table Data:
Name   StaffNumber     Senior
Alison 456             888
Mark   123             456
Jim    111             456
Paul   221             111
Kent   214             111
0
Question by:wolivier69
• 4
• 4
• 2
• +1

LVL 23

Expert Comment

ID: 35481635
See these queries with sample data
create table #employees
(
Name		varchar(20),
StaffNumber	int,
Senior		int
)

insert into #employees
select 'Alison', 456,             888 union all
select 'Mark',   123,             456 union all
select 'Jim',    111,             456 union all
select 'Paul',   221,             111 union all
select 'Kent',   214,             111

select * from #employees

-- Alison
declare @StaffNumber int
set @StaffNumber = 456
select * from #employees where StaffNumber = 456 or Senior = 456

-- Jim
declare @StaffNumber int
set @StaffNumber = 111
select * from #employees where StaffNumber = 111 or Senior = 111

drop table #employees

Raj
0

LVL 23

Expert Comment

ID: 35481693
If you are passing Name instead of StaffNumber, try this way

declare @Name varchar(20)
set @Name = 'Alison'
declare @StaffNumber int
select @StaffNumber = StaffNumber from #employees WHERE Name = @Name

select * from #employees where StaffNumber = @StaffNumber or Senior = @StaffNumber

Modify tablename #employees to your actual table name

Raj
0

LVL 9

Accepted Solution

kaminda earned 2000 total points
ID: 35481815
Use this query, and replace staff number with a parameter
WITH Children(Name,StaffNumber,Senior)
AS(
SELECT Name, StaffNumber,Senior
From temployees where StaffNumber = 111
UNION all
SELECT m.Name, m.StaffNumber, m.Senior FROM temployees m
INNER JOIN children l
ON l.StaffNumber = m.Senior
)

SELECT * FROM Children
0

LVL 11

Expert Comment

ID: 35481816
0

Author Comment

ID: 35481938
My StaffNumber is a type nvarchar(50)

I tired this but get a System.OutOfMemoryExceptioin

ALTER PROCEDURE dbo.ShowHierarchy
@Root nvarchar(50)
AS
AS(
From StaffPrintingDetailsInferno where StaffNumber = @Root
UNION all
SELECT m.PRINTUSERNAME, m.StaffNumber, m.Senior FROM StaffPrintingDetailsInferno m
INNER JOIN children l
ON l.StaffNumber = m.Senior
)

SELECT * FROM Children

/* SET NOCOUNT ON */
RETURN
0

LVL 9

Expert Comment

ID: 35481969
Can you run the query in SSMS without an error? Is your application only returning this errror?
0

Author Comment

ID: 35481994
I get this error while running it in a stored procedure using visual studio will try in ssms
0

LVL 9

Expert Comment

ID: 35482034
Most of the times it is not because of the SQL Server this happens but the .NET objects which are not properly diposed cause this System.OutOfMemoryExceptioin.

0

Author Comment

ID: 35482038
Seems to work in SSMS need to still test it a bit and will accept your solution but do you know why I get an error while running it in visual studio?
0

LVL 9

Expert Comment

ID: 35482082
This will be a good starting point for you...

http://sql-server-performance.com/Community/forums/p/18337/106039.aspx

But seems to be alot of reasons for this to happen in .NET
0

Author Comment

ID: 35482126
thanks a lot for all your help
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that witâ€¦
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
###### Suggested Courses
Course of the Month8 days, 19 hours left to enroll