Difference between revisions of "Dbo.uspGetManagerEmployees (procedure)"

From dbscript Online Help
Jump to: navigation, search
(New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''procedure | dbo.uspGetManagerEmployees |- |} <pre> CREATE PROCEDURE [dbo].[uspGetMana...)
 
 
(7 intermediate revisions by the same user not shown)
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 5: Line 73:
 
| '''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 43: Line 115:
 
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]]
 +
 +
|}

Latest revision as of 00:15, 24 June 2010

wikibot[edit]

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

Source[edit]

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[edit]

Dependency Type Object Type Referenced Object
Select Table HumanResources.Employee
Select Table Person.Contact


automatically generated[edit]

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
Retrieved from "http://dbshelp.devio.at/index.php?title=Dbo.uspGetManagerEmployees_(procedure)&oldid=2591"