Northwind Database (Document! X Sample)
dbo.uspGetEmployeeManagers Stored Procedure
AdventureWorks Database > dbo Schema : dbo.uspGetEmployeeManagers Stored Procedure
Description
Stored procedure using a recursive query to return the direct and indirect managers of the specified employee.
Properties
Creation Date27/10/2017 14:33
Encrypted
Ansi Nulls
Parameters
ParameterDirectionDescriptionData TypeSize
InInput parameter for the stored procedure uspGetEmployeeManagers. Enter a valid BusinessEntityID from the HumanResources.Employee table.Integer4
Return Value Integer4
Objects that dbo.uspGetEmployeeManagers depends on
 Database ObjectObject TypeDescriptionDep Level
Person.BusinessEntity tablePerson.BusinessEntityTableSource of the ID that connects vendors, customers, and employees with address and contact information.2
HumanResources.Employee tableHumanResources.EmployeeTableEmployee information such as salary, department, and title.1
dbo.Flag datatypedbo.FlagUser Defined Data Type 2
dbo.Name datatypedbo.NameUser Defined Data Type 2
dbo.NameStyle datatypedbo.NameStyleUser Defined Data Type 2
Person.Person tablePerson.PersonTableHuman beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.1
Procedure Source Code
CREATE PROCEDURE [dbo].[uspGetEmployeeManagers]
    @BusinessEntityID [int]
AS

BEGIN
    SET NOCOUNT ON;

    -- Use recursive query to list out all Employees required for a particular Manager
    WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [JobTitle], [RecursionLevel]) -- CTE name and columns
    AS (
        SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], e.[JobTitle], 0 -- Get the initial Employee
        FROM [HumanResources].[Employee] e 
            INNER JOIN [Person].[Person] as p
            ON p.[BusinessEntityID] = e.[BusinessEntityID]
        WHERE e.[BusinessEntityID] = @BusinessEntityID
        UNION ALL
        SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], e.[JobTitle], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [HumanResources].[Employee] e 
            INNER JOIN [EMP_cte]
            ON e.[OrganizationNode] = [EMP_cte].[OrganizationNode].GetAncestor(1)
            INNER JOIN [Person].[Person] p 
            ON p.[BusinessEntityID] = e.[BusinessEntityID]
    )
    -- Join back to Employee to return the manager name 
    SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[BusinessEntityID], [EMP_cte].[FirstName], [EMP_cte].[LastName], 
        [EMP_cte].[OrganizationNode].ToString() AS [OrganizationNode], p.[FirstName] AS 'ManagerFirstName', p.[LastName] AS 'ManagerLastName'  -- Outer select from the CTE
    FROM [EMP_cte] 
        INNER JOIN [HumanResources].[Employee] e 
        ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
        INNER JOIN [Person].[Person] p 
        ON p.[BusinessEntityID] = e.[BusinessEntityID]
    ORDER BY [RecursionLevel], [EMP_cte].[OrganizationNode].ToString()
    OPTION (MAXRECURSION 25) 
END;
See Also

Related Objects

dbo Schema
AdventureWorks Database