Manager Hierarchy (Recursive)

[jzdoh]

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.

[Infide]

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..

CREATE FUNCTION dbo.GetHierarchy(@ManagerID int)
RETURNS @Hierarchy table (ParentID int,
                   ChildID int)
AS
BEGIN
 
    --EMPNUM, --MANAGEREMPNUM, --TblEmployee
 
    declare @ChildID int
    if (@ManagerID int is null)
    begin
        declare curEmp cursor local fast_forward for 
        select empNum
        from tblEmployee
 
        open curEmp
 
        fetchnext 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.GetHierarchy(@ChildID)
 
        fetchnext from curEmp into @ChildID
        end
 
        close curEmp
        deallocate curEmp
    end
    else
    begin
        declare curEmp cursor local faast_forward for 
        select empNum
        from tblEmployee
        where managerempnum = @ManagerID
 
        open curEmp
 
        fetchnext 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.GetHierarchy(@ChildID)
 
        fetchnext from curemp into @ChildID
 
        end
 
 
 
    end
    return
 
end
 

[Infide]

Or better yet, if you're using sql server 2005 I just found this in the documentation.

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports ;
GO
 
 
 

Very nice

[jzdoh]

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