Here is what it looks like in the script

USE [AdventureWorks]

GO

/****** Object: StoredProcedure [zz_RecycleBin].[dbo_$_uspGetManagerEmployees_$_TOSHL35@dad_$_2009_06_07T23_55_38_100] Script Date: 06/08/2009 09:24:18 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [zz_RecycleBin].[dbo_$_uspGetManagerEmployees_$_TOSHL35@dad_$_2009_06_07T23_55_38_100]

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