Latest revision |
Your text |
Line 1: |
Line 1: |
| == wikibot == | | == wikibot == |
− |
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- valign="top"
| |
− | | '''Procedure
| |
− | | dbo.uspGetManagerEmployees
| |
− | |- valign="top"
| |
− | | '''Description
| |
− | | Stored procedure using a recursive query to return the direct and indirect employees of the specified manager.
| |
− | |}
| |
− |
| |
− | === Source ===
| |
− | <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>
| |
− |
| |
− | === References ===
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- style="background:silver"
| |
− | | '''Dependency Type
| |
− | | '''Object Type
| |
− | | '''Referenced Object
| |
− |
| |
− | |- valign="top"
| |
− | | Select
| |
− | | Table
| |
− | | [[HumanResources.Employee_(table)|HumanResources.Employee]]
| |
− |
| |
− | |- valign="top"
| |
− | | Select
| |
− | | Table
| |
− | | [[Person.Contact_(table)|Person.Contact]]
| |
− |
| |
− | |}
| |
− |
| |
− |
| |
− | == automatically generated ==
| |
| | | |
| {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" | | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |
Line 73: |
Line 5: |
| | '''procedure | | | '''procedure |
| | dbo.uspGetManagerEmployees | | | dbo.uspGetManagerEmployees |
− | |- valign="top"
| |
− | | '''Description
| |
− | | Stored procedure using a recursive query to return the direct and indirect employees of the specified manager.
| |
| |- | | |- |
| |} | | |} |
| | | |
| <pre> | | <pre> |
− |
| |
| CREATE PROCEDURE [dbo].[uspGetManagerEmployees] | | CREATE PROCEDURE [dbo].[uspGetManagerEmployees] |
| @ManagerID [int] | | @ManagerID [int] |
Line 115: |
Line 43: |
| END; | | END; |
| </pre> | | </pre> |
− |
| |
− |
| |
− | {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
| |
− | |- style="background:silver"
| |
− | | '''Dependency Type
| |
− | | '''Object Type
| |
− | | '''Referenced Object
| |
− |
| |
− | |-
| |
− | | Select
| |
− | | Table
| |
− | | [[HumanResources.Employee_(table)|HumanResources.Employee]]
| |
− |
| |
− | |-
| |
− | | Select
| |
− | | Table
| |
− | | [[Person.Contact_(table)|Person.Contact]]
| |
− |
| |
− | |}
| |