dbo.uspGetManagerEmployees Stored Procedure

Description

Stored procedure using a recursive query to return the direct and indirect employees of the specified manager.

Properties

Creation Date	08/01/2010 08:41
Encrypted
Ansi Nulls

Parameters

Parameter	Direction	Description	Data Type	Size
@ManagerID	In	Input parameter for the stored procedure uspGetManagerEmployees. Enter a valid ManagerID from the HumanResources.Employee table.	int	4
@RETURN_VALUE	Return Value		int	4

Objects that dbo.uspGetManagerEmployees depends on

Database Object	Object Type	Description	Dep Level
Person.Contact	Table	Names of each employee, customer contact, and vendor contact.	1
HumanResources.Employee	Table	Employee information such as salary, department, and title.	1
dbo.Flag	User Defined Data Type		2
dbo.Name	User Defined Data Type		2
dbo.NameStyle	User Defined Data Type		2
dbo.Phone	User Defined Data Type		2

Procedure Source Code

CREATE PROCEDURE [dbo].[uspGetManagerEmployees]
@ManagerID [int]
AS

BEGIN
SET NOCOUNT ON;
WITH [EMP_cte]([EmployeeID], [ManagerID], [FirstName], [LastName], [RecursionLevel]) -- CTE name and columns
AS (
SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], 0 -- Get the initial list of Employees for Manager n
FROM [HumanResources].[Employee] e 
INNER JOIN [Person].[Contact] c 
ON e.[ContactID] = c.[ContactID]
WHERE [ManagerID] = @ManagerID
UNION ALL
SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], [RecursionLevel] + 1 -- Join recursive member to anchor
FROM [HumanResources].[Employee] e 
INNER JOIN [EMP_cte]
ON e.[ManagerID] = [EMP_cte].[EmployeeID]
INNER JOIN [Person].[Contact] c 
ON e.[ContactID] = c.[ContactID]
)
SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[ManagerID], c.[FirstName] AS 'ManagerFirstName', c.[LastName] AS 'ManagerLastName',
[EMP_cte].[EmployeeID], [EMP_cte].[FirstName], [EMP_cte].[LastName] -- Outer select from the CTE
FROM [EMP_cte] 
INNER JOIN [HumanResources].[Employee] e 
ON [EMP_cte].[ManagerID] = e.[EmployeeID]
INNER JOIN [Person].[Contact] c 
ON e.[ContactID] = c.[ContactID]
ORDER BY [RecursionLevel], [ManagerID], [EmployeeID]
OPTION (MAXRECURSION 25) 
END;

Related Objects

dbo Schema
AdventureWorks Database