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