Difference between revisions of "Dbo.uspGetEmployeeManagers (procedure)"
| Line 43: | Line 43: | ||
END; | END; | ||
</pre> | </pre> | ||
| − | |||
== automatically generated == | == automatically generated == | ||
| Line 50: | Line 49: | ||
| '''procedure | | '''procedure | ||
| dbo.uspGetEmployeeManagers | | dbo.uspGetEmployeeManagers | ||
| − | |- | + | |- valign="top" |
| '''Description | | '''Description | ||
| Line 101: | Line 100: | ||
| '''Object Type | | '''Object Type | ||
| '''Dependency Type | | '''Dependency Type | ||
| − | + | ||
|- | |- | ||
| [[HumanResources.Employee_(table)|HumanResources.Employee]] | | [[HumanResources.Employee_(table)|HumanResources.Employee]] | ||
Revision as of 06:25, 30 November 2009
wikibot
| procedure | dbo.uspGetEmployeeManagers |
CREATE PROCEDURE [dbo].[uspGetEmployeeManagers]
@EmployeeID [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], [Title], [RecursionLevel]) -- CTE name and columns
AS (
SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], e.[Title], 0 -- Get the initial Employee
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON e.[ContactID] = c.[ContactID]
WHERE e.[EmployeeID] = @EmployeeID
UNION ALL
SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], e.[Title], [RecursionLevel] + 1 -- Join recursive member to anchor
FROM [HumanResources].[Employee] e
INNER JOIN [EMP_cte]
ON e.[EmployeeID] = [EMP_cte].[ManagerID]
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].[EmployeeID], [EMP_cte].[FirstName], [EMP_cte].[LastName],
[EMP_cte].[ManagerID], c.[FirstName] AS 'ManagerFirstName', c.[LastName] AS 'ManagerLastName' -- 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;
automatically generated
| procedure | dbo.uspGetEmployeeManagers |
| Description | Stored procedure using a recursive query to return the direct and indirect managers of the specified employee. |
CREATE PROCEDURE [dbo].[uspGetEmployeeManagers]
@EmployeeID [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], [Title], [RecursionLevel]) -- CTE name and columns
AS (
SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], e.[Title], 0 -- Get the initial Employee
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON e.[ContactID] = c.[ContactID]
WHERE e.[EmployeeID] = @EmployeeID
UNION ALL
SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], e.[Title], [RecursionLevel] + 1 -- Join recursive member to anchor
FROM [HumanResources].[Employee] e
INNER JOIN [EMP_cte]
ON e.[EmployeeID] = [EMP_cte].[ManagerID]
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].[EmployeeID], [EMP_cte].[FirstName], [EMP_cte].[LastName],
[EMP_cte].[ManagerID], c.[FirstName] AS 'ManagerFirstName', c.[LastName] AS 'ManagerLastName' -- 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;
| Referenced Object | Object Type | Dependency Type |
| HumanResources.Employee | Table | Select |
| Person.Contact | Table | Select |
