?
Solved

Employee hierarchy tree

Posted on 2011-04-28
11
Medium Priority
?
730 Views
Last Modified: 2013-12-16
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

Open in new window

0
Comment
Question by:wolivier69
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
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

Open in new window


Raj
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
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

Open in new window


Modify tablename #employees to your actual table name

Raj
0
 
LVL 9

Accepted Solution

by:
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

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 11

Expert Comment

by:bmatumbura
ID: 35481816
0
 

Author Comment

by:wolivier69
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
  WITH Children(PRINTUSERNAME,StaffNumber,Senior)
			AS(
			SELECT PRINTUSERNAME, StaffNumber,Senior
			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

Open in new window

0
 
LVL 9

Expert Comment

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

Author Comment

by:wolivier69
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

by:kaminda
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

by:wolivier69
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

by:kaminda
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

by:wolivier69
ID: 35482126
thanks a lot for all your help
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question