Procedure: uspGetManagerEmployees

Properties

Property	Value
Name	uspGetManagerEmployees
ID	151671588
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 employees of the specified manager.

Permissions

No object level permissions defined.

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 uspGetManagerEmployees. Enter a valid BusinessEntityID of the manager from the HumanResources.Employee table.

Objects that depend on [uspGetManagerEmployees]

dbo.uspGetManagerEmployees

Objects on which [uspGetManagerEmployees] depends

dbo.uspGetManagerEmployees
- 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].[uspGetManagerEmployees] @BusinessEntityID [int] AS BEGIN SET NOCOUNT ON; WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel]) -- CTE name and columns AS ( SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], 0 -- Get the initial list of Employees for Manager n FROM [HumanResources].[Employee] e INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID] WHERE e.[BusinessEntityID] = @BusinessEntityID UNION ALL SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], [RecursionLevel] + 1 -- Join recursive member to anchor 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] ) SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[OrganizationNode].ToString() as [OrganizationNode], p.[FirstName] AS 'ManagerFirstName', p.[LastName] AS 'ManagerLastName', [EMP_cte].[BusinessEntityID], [EMP_cte].[FirstName], [EMP_cte].[LastName] -- 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 employees of the specified manager.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'uspGetManagerEmployees' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Input parameter for the stored procedure uspGetManagerEmployees. Enter a valid BusinessEntityID of the manager from the HumanResources.Employee table.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'uspGetManagerEmployees', @level2type=N'PARAMETER',@level2name=N'@BusinessEntityID'

Script

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [dbo].[uspGetManagerEmployees] @BusinessEntityID [int] AS BEGIN SET NOCOUNT ON; WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel]) -- CTE name and columns AS ( SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], 0 -- Get the initial list of Employees for Manager n FROM [HumanResources].[Employee] e INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID] WHERE e.[BusinessEntityID] = @BusinessEntityID UNION ALL SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], [RecursionLevel] + 1 -- Join recursive member to anchor 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] ) SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[OrganizationNode].ToString() as [OrganizationNode], p.[FirstName] AS 'ManagerFirstName', p.[LastName] AS 'ManagerLastName', [EMP_cte].[BusinessEntityID], [EMP_cte].[FirstName], [EMP_cte].[LastName] -- 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 employees of the specified manager.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'uspGetManagerEmployees'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Input parameter for the stored procedure uspGetManagerEmployees. Enter a valid BusinessEntityID of the manager from the HumanResources.Employee table.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'uspGetManagerEmployees', @level2type=N'PARAMETER',@level2name=N'@BusinessEntityID'