asked on

Employee hierarchy tree

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

Rajkumar Gs

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

Rajkumar Gs

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

ASKER CERTIFIED SOLUTION

kaminda

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bmatumbura

This may help:

http://www.sqlteam.com/article/more-trees-hierarchies-in-sql

wolivier69

ASKER

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

kaminda

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

wolivier69

ASKER

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

kaminda

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.

wolivier69

ASKER

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?

kaminda

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

wolivier69

ASKER

thanks a lot for all your help