# Procedure: uspGetEmployeeManagers

## Properties

Property | Value
--- | ---
Name | uspGetEmployeeManagers
ID | 135671531
Schema | dbo
Owner | dbo
Type | SQL STORED PROCEDURE
Execute As | Caller
Encrypted |
ANSI NULLS |
Uses Quoted Identifier |
Published |
Schema Published |
Execution Replicated |
Replication Serializable Only |
Skips Replication Constraints |
Is Recompiled |
Uses Database Collation |
Null on Null Input |
MS Shipped |
Modify Date | 8/8/2010 5:23:46 PM
Create Date | 8/8/2010 5:23:46 PM

## Extended Properties

Name | Value
--- | ---
MS_Description | Stored procedure using a recursive query to return the direct and indirect managers of the specified employee.

## Permissions

#### Permissions for: duser

Permission | Grantor | Grant | With Grant Option | Deny
--- | --- | --- | --- | ---
Alter | dbo |  |  |
Control | dbo |  |  |
Execute | dbo |  |  |
Take Ownership | dbo |  |  |
View Definition | dbo |  |  |

#### Permissions for: JohnBlack

Permission | Grantor | Grant | With Grant Option | Deny
--- | --- | --- | --- | ---
Alter | dbo |  |  |
Control | dbo |  |  |
Execute | dbo |  |  |
Take Ownership | dbo |  |  |
View Definition | dbo |  |  |

## Parameters

Parameter Name | Datatype | Output | Cursor Ref | Readonly | Has Default Value | Default Value | Xml Collection Name | Description
--- | --- | --- | --- | --- | --- | --- | --- | ---
@BusinessEntityID | int |  |  |  |  |  |  | Input parameter for the stored procedure uspGetEmployeeManagers. Enter a valid BusinessEntityID from the HumanResources.Employee table.

## Objects that depend on [uspGetEmployeeManagers]

- dbo.uspGetEmployeeManagers

## Objects on which [uspGetEmployeeManagers] depends

- dbo.uspGetEmployeeManagers
  - HumanResources.Employee
    - dbo.Flag
    - Person.Person
      - dbo.Name
      - dbo.NameStyle
      - Demographics.exist
      - Person.AdditionalContactInfoSchemaCollection
      - Person.BusinessEntity
      - Person.IndividualSurveySchemaCollection

## SQL

Script
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE PROCEDURE [dbo].[uspGetEmployeeManagers] @BusinessEntityID [int] AS BEGIN SET NOCOUNT ON; 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] ) 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; EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stored procedure using a recursive query to return the direct and indirect managers of the specified employee.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'uspGetEmployeeManagers' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Input parameter for the stored procedure uspGetEmployeeManagers. Enter a valid BusinessEntityID from the HumanResources.Employee table.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'uspGetEmployeeManagers', @level2type=N'PARAMETER',@level2name=N'@BusinessEntityID'

#### See also

List of Procedures