Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
- RETURNS @retFindReports TABLE
- (
- EmployeeID int primary key NOT NULL,
- FirstName nvarchar(255) NOT NULL,
- LastName nvarchar(255) NOT NULL,
- JobTitle nvarchar(50) NOT NULL,
- RecursionLevel int NOT NULL
- )
- --Returns a result set that lists all the employees who report to the
- --specific employee directly or indirectly.*/
- AS
- BEGIN
- WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
- AS (
- -- Get the initial list of Employees for Manager n
- SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0
- FROM HumanResources.Employee e
- INNER JOIN Person.Person p
- ON p.BusinessEntityID = e.BusinessEntityID
- WHERE e.BusinessEntityID = @InEmpID
- UNION ALL
- -- Join recursive member to anchor
- SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1
- FROM HumanResources.Employee e
- INNER JOIN EMP_cte
- ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
- INNER JOIN Person.Person p
- ON p.BusinessEntityID = e.BusinessEntityID
- )
- -- copy the required columns to the result of the function
- INSERT @retFindReports
- SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
- FROM EMP_cte
- RETURN
- END;
- GO
- -- Example invocation
- SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
- FROM dbo.ufn_FindReports(1);
- GO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement