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'

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'