Dbo.uspGetManagerEmployees (procedure) - Revision history

Bot at 22:15, 23 June 2010

2010-06-23T22:15:43Z

Bot at 21:59, 23 June 2010

2010-06-23T21:59:54Z

Bot at 22:07, 2 February 2010

2010-02-02T22:07:46Z

Bot at 21:56, 28 December 2009

2009-12-28T21:56:43Z

Bot at 06:00, 30 November 2009

2009-11-30T06:00:53Z

Bot at 05:39, 30 November 2009

2009-11-30T05:39:04Z

Bot at 05:25, 30 November 2009

2009-11-30T05:25:32Z

Bot at 23:00, 26 November 2009

2009-11-26T23:00:04Z

Bot: New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''procedure | dbo.uspGetManagerEmployees |- |}

 CREATE PROCEDURE [dbo].[uspGetMana...

2009-01-03T12:36:14Z

New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''procedure | dbo.uspGetManagerEmployees |- |} <pre> CREATE PROCEDURE [dbo].[uspGetMana...

New page

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|-
| '''procedure
| dbo.uspGetManagerEmployees
|-
|}

<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>

@@ Line 57: / Line 57: @@
 | Select
 | Table
-| [[HumanResources.Employee_(_(table))|HumanResources.Employee]]
+| [[HumanResources.Employee_(table)|HumanResources.Employee]]
 |- valign="top"
 | Select
 | Table
-| [[Person.Contact_(_(table))|Person.Contact]]
+| [[Person.Contact_(table)|Person.Contact]]
 |}

@@ Line 2: / Line 2: @@
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
-|-
+|- valign="top"
 | '''Procedure
 | dbo.uspGetManagerEmployees
@@ Line 8: / Line 8: @@
 | '''Description
 | Stored procedure using a recursive query to return the direct and indirect employees of the specified manager.
 |}
 === Source ===
@@ Line 56: / Line 54: @@
 | '''Referenced Object
-|-
+|- valign="top"
 | Select
 | Table
-| [[HumanResources.Employee_(table)|HumanResources.Employee]]
+| [[HumanResources.Employee_(_(table))|HumanResources.Employee]]
-|-
+|- valign="top"
 | Select
 | Table
-| [[Person.Contact_(table)|Person.Contact]]
+| [[Person.Contact_(_(table))|Person.Contact]]
 |}

@@ Line 3: / Line 3: @@
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 |-
-| '''procedure
+| '''Procedure
 | dbo.uspGetManagerEmployees
 |- valign="top"
@@ Line 11: / Line 11: @@
 |}
 <pre>
 CREATE PROCEDURE [dbo].[uspGetManagerEmployees]
@@ Line 47: / Line 49: @@
 </pre>
+=== References ===
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 |- style="background:silver"
@@ Line 65: / Line 67: @@
 |}

@@ Line 5: / Line 5: @@
 | '''procedure
 | dbo.uspGetManagerEmployees
 |-
 |}
@@ Line 43: / Line 46: @@
 END;
 </pre>
 == automatically generated ==

@@ Line 50: / Line 50: @@
 | dbo.uspGetManagerEmployees
 |- valign="top"
 | '''Description
 | Stored procedure using a recursive query to return the direct and indirect employees of the specified manager.
 |-
 |}

← Older revision		Revision as of 23:00, 26 November 2009
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
		+	\|}