Difference between revisions of "Dbo.uspGetManagerEmployees (procedure)"
(New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''procedure | dbo.uspGetManagerEmployees |- |} <pre> CREATE PROCEDURE [dbo].[uspGetMana...) |
|||
Line 43: | Line 43: | ||
END; | END; | ||
</pre> | </pre> | ||
+ | |||
+ | == automatically generated == | ||
+ | |||
+ | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
+ | |- | ||
+ | | '''procedure | ||
+ | | dbo.uspGetManagerEmployees | ||
+ | |- | ||
+ | |||
+ | | '''Description | ||
+ | | Stored procedure using a recursive query to return the direct and indirect employees of the specified manager. | ||
+ | |- | ||
+ | |||
+ | |} | ||
+ | |||
+ | <pre> | ||
+ | |||
+ | 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; | ||
+ | </pre> | ||
+ | |||
+ | |||
+ | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | ||
+ | |- style="background:silver" | ||
+ | | '''Referenced Object | ||
+ | | '''Object Type | ||
+ | | '''Dependency Type | ||
+ | |||
+ | |- | ||
+ | | [[HumanResources.Employee_(table)|HumanResources.Employee]] | ||
+ | | Table | ||
+ | | Select | ||
+ | |- | ||
+ | | [[Person.Contact_(table)|Person.Contact]] | ||
+ | | Table | ||
+ | | Select | ||
+ | |} |
Revision as of 00:00, 27 November 2009
wikibot
procedure | dbo.uspGetManagerEmployees |
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;
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;
Referenced Object | Object Type | Dependency Type |
HumanResources.Employee | Table | Select |
Person.Contact | Table | Select |