Manager Hierarchy (Recursive)

[jzdoh]

I am trying to write a stored procedure that could create a table called tblManagerHiera rchy. 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 tblManagerHiera rchy 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.

tblManagerHiera rchy (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 tblManagerHiera rchy. 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 tblManagerHiera rchy 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.

tblManagerHiera rchy (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.fnPMMgrHier archy(@ManagerI D 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(Pare ntID,ChildID)
values(@Manager ID,@ChildID)

insert into @Hierarchy(Pare ntID,ChildID)
select * from dbo.fnPMMgrHier archy(@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 sTmShtSupvEmpNu m = @ManagerID

open @curEmp

fetch next from @curEmp into @ChildID
while @@fetch_status = 0
begin
insert into @Hierarchy(Pare ntID,ChildID)
values (@ManagerID,@Ch ildID)

insert into @Hierarchy(pare ntID,childID)
select * from dbo.fnPMMgrHier archy(@ChildID)

fetch next from @curEmp into @ChildID

end

end
return

end