title: Need to find the full team under a manager
body: <p>I need to write a SP to find the full team under manager X. Full team in the sense, I would like to find all the direct and indirect reportees under a manager X. I am using SQL Server 2005. </p>
  
  <p>The Employee table Structure is:</p>
  
  <pre><code>EmployeeNumber  FirstName  LastName  ManagerEmployeeNumber
  </code></pre>
  
  <p>For example, if SP has the input as Employee Number = 10211, then the output should be like this:</p>
  
  <pre><code>EmployeeNumber  FirstName  LastName  ManagerEmployeeNumber
  13903           Anil       S         10211
  1260            David      D         13903
  8079            Donald     K         1260
  </code></pre>
  
  <p>I have written the needed things to find the full team under manager X in a function as:</p>
  
  <pre><code>ALTER FUNCTION [dbo].[FullTeamUnderManager1](@ManagerId int)  
  RETURNS   
  @EmployeeList TABLE   
  ( 
   EmployeeName Varchar(200) 
  )  
  AS  
  BEGIN  
  
   With hierarchy(EmployeeName, Employee_Id, SupEmp_Id, Depth) as  
   (  
    Select (first_name + ', ' + last_name) EmployeeName, employee_number, employee_number as supemp_id, 0 as depth  
       From dbo.employee  
       Where employee_number = @ManagerId  
    union all   
    Select (child.first_name + ', ' + child.last_name), child.employee_number, child.manager_employee_number, parent.depth + 1   
    From dbo.employee As child  
    Inner Join hierarchy As parent  
        On child.manager_employee_number = parent.Employee_Id  
       Where child.employee_number != parent.Employee_Id  
  
   )  
   insert into @EmployeeList(EmployeeName)  
    select EmployeeName from hierarchy where depth &gt; 0  
    order by EmployeeName
   RETURN   
  END 
  </code></pre>
  
  <p>I need to combine it with the below SP. The output should be a single column as 'EmployeeName'</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[spGetCorpDirDistributionTableUsers]            
  @activeOnly bit = 1,            
  @employeesOnly bit = 1,            
  @isAllowedNonEmployees bit = 0,      
  @isAllowedNonEmployeesInOrgChart bit = 0, 
  @login_employee_number INT = NULL,      
  @IsGetLocations bit = 0,
  @Location_IDLIST VARCHAR(MAX) = NULL,
  @IsGetDepartments bit = 0,
  @Department_IDLIST VARCHAR(MAX) = NULL,
  @IsGetDirectReports bit = 0,
  @IsGetDirectReportsManagers bit = 0,
  @IsGetAllManagers bit=0,
  @manager_employee_number INT
  
     AS    
     DECLARE @employeeTypeId int       
  
     SET @employeeTypeId = 1           
     SET @isAllowedNonEmployees = ISNULL(@isAllowedNonEmployees, 0) 
     SET @isAllowedNonEmployeesInOrgChart  = ISNULL(@isAllowedNonEmployeesInOrgChart, 0)               
     SET @IsGetDirectReports = ISNULL(@IsGetDirectReports, 0) 
     SET @IsGetDirectReportsManagers  = ISNULL(@IsGetDirectReportsManagers, 0)
  
     SELECT         
      (e1.first_name + ', ' + e1.last_name) EmployeeName,
     FROM EMPLOYEE e1           
     LEFT OUTER JOIN EMPLOYEE e2 on e1.manager_employee_number = e2.employee_number             
     INNER JOIN LOCATION loc ON e1.location_id = loc.location_id   
     INNER JOIN DEPARTMENT dep ON e1.department_id = dep.department_id                  
     LEFT OUTER JOIN EMPLOYEE_TYPE emptype ON e1.employee_type_id = emptype.employee_type_id            
     WHERE          
       e1.visible = 1 and             
       (@activeOnly = 0 OR @activeOnly IS NULL OR e1.active_flag = 1) and            
       (@isAllowedNonEmployees = 1 or e1.employee_type_id = @employeeTypeId) and             
       (@employeesOnly = 0 or e1.employee_type_id = @employeeTypeId) and  
  
  
  
    --Location
    (@IsGetLocations=0 OR
    (COALESCE(@Location_IDLIST,'%') = '%' OR e1.location_id IN (SELECT Segment FROM dbo.fn_GetParsedValues(@Location_IDLIST,',')))) and
  
    --Department
    (@IsGetDepartments=0 OR
    (COALESCE(@Department_IDLIST,'%') = '%' OR e1.Department_ID IN (SELECT Segment FROM dbo.fn_GetParsedValues(@Department_IDLIST,',')))) and      
  
    --IsGetDirectReports
    (@IsGetDirectReports=0 OR
      ((e1.manager_employee_number = @manager_employee_number and e1.active_flag = 1 and e1.visible = 1)    
        and ((@isAllowedNonEmployees = 1 and  @isAllowedNonEmployeesInOrgChart = 1) or e1.employee_type_id = @employeeTypeId))) and
  
    --IsGetDirectReportsManagers
    (@IsGetDirectReportsManagers=0 OR
    ((e1.manager_employee_number = @manager_employee_number and e1.active_flag = 1 and e1.visible = 1)    
        and ((@isAllowedNonEmployees = 1 and  @isAllowedNonEmployeesInOrgChart = 1) or e1.employee_type_id = @employeeTypeId)
        and (select count(*) from employee e3 where manager_employee_number = e1.employee_number and active_flag = 1 and ((@isAllowedNonEmployees = 1 and  @isAllowedNonEmployeesInOrgChart = 1) or e3.employee_type_id = @employeeTypeId))&gt;0
      )) and
  
    --IsGetAllManagers
    (@IsGetAllManagers=0 OR
    ((SELECT count(*) FROM dbo.employee e2 WHERE e2.manager_employee_number = e1.employee_number ) &gt; 0) AND
      @IsGetDirectReports=0 AND @IsGetDirectReportsManagers=0)
  
  order by EmployeeName
  </code></pre>
  
  <p>Anybody please helpout... Thanks in advance...</p>