Difference between revisions of "Dbo.uspGetManagerEmployees (procedure)"
| Line 3: | Line 3: | ||
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
|- | |- | ||
| − | | ''' | + | | '''Procedure |
| dbo.uspGetManagerEmployees | | dbo.uspGetManagerEmployees | ||
|- valign="top" | |- valign="top" | ||
| Line 11: | Line 11: | ||
|} | |} | ||
| + | |||
| + | === Source === | ||
<pre> | <pre> | ||
CREATE PROCEDURE [dbo].[uspGetManagerEmployees] | CREATE PROCEDURE [dbo].[uspGetManagerEmployees] | ||
| Line 47: | Line 49: | ||
</pre> | </pre> | ||
| − | + | === References === | |
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
|- style="background:silver" | |- style="background:silver" | ||
| Line 65: | Line 67: | ||
|} | |} | ||
| − | |||
Revision as of 23:07, 2 February 2010
wikibot
| Procedure | dbo.uspGetManagerEmployees |
| Description | Stored procedure using a recursive query to return the direct and indirect employees of the specified manager. |
Source
CREATE PROCEDURE [dbo].[uspGetManagerEmployees]
@ManagerID [int]
AS
BEGIN
SET NOCOUNT ON;
-- Use recursive query to list out all Employees required for a particular Manager
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]
)
-- Join back to Employee to return the manager name
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;
References
| Dependency Type | Object Type | Referenced Object |
| Select | Table | HumanResources.Employee |
| Select | Table | Person.Contact |
automatically generated
| procedure | dbo.uspGetManagerEmployees |
| Description | Stored procedure using a recursive query to return the direct and indirect employees of the specified manager. |
CREATE PROCEDURE [dbo].[uspGetManagerEmployees]
@ManagerID [int]
AS
BEGIN
SET NOCOUNT ON;
-- Use recursive query to list out all Employees required for a particular Manager
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]
)
-- Join back to Employee to return the manager name
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;
| Dependency Type | Object Type | Referenced Object |
| Select | Table | HumanResources.Employee |
| Select | Table | Person.Contact |
