469,588 Members | 2,688 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,588 developers. It's quick & easy.

Manager Hierarchy (Recursive)

2
I am trying to write a stored procedure that could create a table called tblManagerHierarchy. It is a table that contains recursive data.
The data is coming from tblEmployee where it contains that EmpNum and ManagerEmpNum.

tblEmployee (EmpNum, ManagerEmpNum)

The tblManagerHierarchy should contain Manager's EmpNum plus direct and indirect subordinates' EmpNum.
Basically it should contain everybody who reports to a Manager directly and indirectly. So CEO should contain everyone in the company, and general manager only has few..

The table's structure should be like this.

tblManagerHierarchy (ManagerEmpNum, EmpNum)

I need to use store procedure that contains cursor and loop.
Any sample of code is appreciated.

Thanks.
Jul 11 '07 #1
3 4789
Infide
28
I am trying to write a stored procedure that could create a table called tblManagerHierarchy. It is a table that contains recursive data.
The data is coming from tblEmployee where it contains that EmpNum and ManagerEmpNum.

tblEmployee (EmpNum, ManagerEmpNum)

The tblManagerHierarchy should contain Manager's EmpNum plus direct and indirect subordinates' EmpNum.
Basically it should contain everybody who reports to a Manager directly and indirectly. So CEO should contain everyone in the company, and general manager only has few..

The table's structure should be like this.

tblManagerHierarchy (ManagerEmpNum, EmpNum)

I need to use store procedure that contains cursor and loop.
Any sample of code is appreciated.

Thanks.
That's quite the task. But if you want a recursive function, here you go..

Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION dbo.GetHierarchy(@ManagerID int)
  2. RETURNS @Hierarchy table (ParentID int,
  3.                    ChildID int)
  4. AS
  5. BEGIN
  6.  
  7.     --EMPNUM, --MANAGEREMPNUM, --TblEmployee
  8.  
  9.     declare @ChildID int
  10.     if (@ManagerID int is null)
  11.     begin
  12.         declare curEmp cursor local fast_forward for 
  13.         select empNum
  14.         from tblEmployee
  15.  
  16.         open curEmp
  17.  
  18.         fetchnext from curEmp into @ChildID
  19.         while @@fetch_status = 0
  20.         begin
  21.             insert into @Hierarchy(ParentID,ChildID)
  22.             values(@ManagerID,@ChildID)
  23.  
  24.             insert into @Hierarchy(ParentID,ChildID)
  25.             select * from dbo.GetHierarchy(@ChildID)
  26.  
  27.         fetchnext from curEmp into @ChildID
  28.         end
  29.  
  30.         close curEmp
  31.         deallocate curEmp
  32.     end
  33.     else
  34.     begin
  35.         declare curEmp cursor local faast_forward for 
  36.         select empNum
  37.         from tblEmployee
  38.         where managerempnum = @ManagerID
  39.  
  40.         open curEmp
  41.  
  42.         fetchnext from curemp into @ChildID
  43.         while @@fetch_status = 0
  44.         begin
  45.             insert into @Hierarchy(ParentID,ChildID)
  46.             values (@ManagerID,@ChildID)
  47.  
  48.             insert into @Hierarchy(parentID,childID)
  49.             select * from dbo.GetHierarchy(@ChildID)
  50.  
  51.         fetchnext from curemp into @ChildID
  52.  
  53.         end
  54.  
  55.  
  56.  
  57.     end
  58.     return
  59.  
  60. end
  61.  
Jul 12 '07 #2
Infide
28
Or better yet, if you're using sql server 2005 I just found this in the documentation.
Expand|Select|Wrap|Line Numbers
  1. USE AdventureWorks;
  2. GO
  3. WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
  4. (
  5.     SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
  6.     FROM HumanResources.Employee
  7.     WHERE ManagerID IS NULL
  8.     UNION ALL
  9.     SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
  10.     FROM HumanResources.Employee e
  11.         INNER JOIN DirectReports d
  12.         ON e.ManagerID = d.EmployeeID 
  13. )
  14. SELECT ManagerID, EmployeeID, EmployeeLevel 
  15. FROM DirectReports ;
  16. GO
  17.  
  18.  
  19.  
Very nice
Jul 12 '07 #3
jzdoh
2
Thank you so much.
I tried your coding, but I got different twist of results than what I expected.
Instead having all the direct and indirect manager's emp num, it listed everyone who is under a manager's chain of command in the relationshiop of Direct Report.

Right Now, it is retreving like this:
MgrEmpNum : EmpNum
CEO's : VP's
VP's : AVPs
AVPs : GM's
GMs : TeamLeader

What I am trying to get is like this:

MgrEmpNum : EmpNum
CEO's : VP's
CEO's : AVPs
CEO's : GM's
CEO's : TeamLeader
.......
VP's : AVPs
VP's : GM's
VP's : TeamLeader

CREATE FUNCTION dbo.fnPMMgrHierarchy(@ManagerID char(11))
RETURNS @Hierarchy table (ParentID char(11), ChildID char(11))
AS
BEGIN

--EMPNUM, --MANAGEREMPNUM, --TblEmployee

declare @ChildID char(11)

If (@ManagerID is null)
begin
declare @curEmp CURSOR
set @curEmp = CURSOR LOCAL FAST_FORWARD FOR
select sEmpNum
from tblPMEmployee

open @curEmp

fetch next from @curEmp into @ChildID
while @@fetch_status = 0
begin
insert into @Hierarchy(ParentID,ChildID)
values(@ManagerID,@ChildID)

insert into @Hierarchy(ParentID,ChildID)
select * from dbo.fnPMMgrHierarchy(@ChildID)

fetch next from @curEmp into @ChildID
end

close @curEmp
deallocate @curEmp
end
else
begin
set @curEmp = CURSOR LOCAL FAST_FORWARD FOR
select sEmpNum
from tblPMEmployee
where sTmShtSupvEmpNum = @ManagerID

open @curEmp

fetch next from @curEmp into @ChildID
while @@fetch_status = 0
begin
insert into @Hierarchy(ParentID,ChildID)
values (@ManagerID,@ChildID)

insert into @Hierarchy(parentID,childID)
select * from dbo.fnPMMgrHierarchy(@ChildID)

fetch next from @curEmp into @ChildID

end

end
return

end
Attached Files
File Type: txt submit.txt (1.4 KB, 368 views)
Jul 12 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Tero Saarni | last post: by
10 posts views Thread by KemperR | last post: by
7 posts views Thread by Kevin Newman | last post: by
7 posts views Thread by Bruce Hensley | last post: by
2 posts views Thread by Tem | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.