Master - Details hierarchical structure in one table

This is a traditional manager-employee hierarchy problem that have many ways of solving it, but I chose implementing it with a SQL function.

We have a need to represent parent - children relationship in one table. The table is relatively simple. In the Employee table we have EmployeeID, Name, DepartmentID, and ManagerID. The ManagerID field links to another EmployeeID in the same table.

The table is easy to implement and development in our application. But when we come to display the data, it's becoming tricky. What we need is to display an Employee and his manager in a report. And a manager could have his upper level manager too. After many hours of search through web, I came up with a function as below:

CREATE FUNCTION dbo.GetEmployeeTree
(@DepartmentID int, @EmployeeID int, @depth int)
RETURNS @EmployeeTree TABLE (EmployeeID int, Depth int, Name nvarchar(50))
AS
BEGIN
DECLARE @ParentID int
DECLARE @nextDepth int
SET @nextDepth = @depth + 1
SET @ParentID = (SELECT ManagerID FROM Employee WHERE EmployeeID = @EmployeeID and DepartmentID =@DepartmentID)

IF @ParentID <> 0
/* We stop if a container has no parent */

INSERT INTO @EmployeeTree (EmployeeID, Depth, Name)
SELECT EmployeeID, @depth As Depth, Name
FROM Employee C
Where C.EmployeeID = @ParentID and C.DepartmentID=@DepartmentID
UNION
--Recurseively call the function to append
SELECT EmployeeID, Depth, Name FROM GetContainerTree(@DepartmentID, @ParentID, @nextDepth)
RETURN
END


To use this function for an employee's manager and upper managers, we need to write a stored procedure or just a simple query like this:


CREATE PROCEDURE dbo.GetManagers
(
@EmployeeID int,
@DepartmentID int
)
AS
select EmployeeID, Name, Depth from GetEmployeeTree(@DepartmentID, @EmployeeID, 0)

RETURN

GO