Here is the two ways to get the memeber level tree structure.
create FUNCTION dbo.MemberTree(@Emp int)
RETURNS @Tree TABLE (EmployeeID int ,Title nvarchar(50),ManagerID int)
AS
BEGIN
DECLARE @Man int
DECLARE @EmpMan int
INSERT @Tree
SELECT EmployeeID,Title,ManagerID
FROM HumanResources.Employee
WHERE EmployeeID=@Emp
SELECT @Man=ManagerID
FROM HumanResources.Employee
WHERE EmployeeID=@Emp
WHILE @@ROWCOUNT >0
BEGIN
set @EmpMan=@Man
INSERT @Tree
SELECT EmployeeID,Title,ManagerID
FROM HumanResources.Employee
WHERE EmployeeID=@EmpMan
SELECT @Man=ManagerID
FROM HumanResources.Employee
WHERE EmployeeID=@EmpMan
END
RETURN
END
After create the above function now run this
SELECT * FROM [dbo].[MemberTree] (1)
Second way to get the memebership tree structure. This function uses recursive call method
create FUNCTION dbo.MemberTreeRecur(@Emp int)
WITH RECOMPILE
RETURNS @Tree TABLE (EmployeeID int ,Title nvarchar(50),ManagerID int)
AS
BEGIN
WITH MemberTreeRecur(EmployeeID ,Title ,ManagerID)
AS (
SELECT EmployeeID,Title,ManagerID
FROM HumanResources.Employee
WHERE EmployeeID=@Emp
UNION ALL
SELECT Node.EmployeeID,Node.Title,Node.ManagerID
FROM HumanResources.Employee Node
JOIN MemberTreeRecur ft
ON Node.EmployeeID=ft.ManagerID
)
insert @Tree
SELECT EmployeeID,Title,ManagerID
FROM MemberTreeRecur
return
END
Now run this
SELECT * FROM [dbo].[MemberTreeRecur] (1)
