title: Get all employee who directly or indirectly reports to an employee, with hierarchy level no
body: <p>I have a Employee table like</p>
  
  <pre><code>emp_id bigint,
  reports_to bigint,
  emp_name varchar(20),
  Constraint [PK_Emp] Primary key (emp_id),
  Constraint [FK_Emp] Foreign key (reports_to) references [MSS].[dbo].[Emp]([emp_id])
  
  emp_id         reports_to        emp_name
  ------         ------       --------------
  1              null         Sumanta
  2              1            Arpita
  3              null         Pradip
  4              1            Sujon
  5              2            Arpan
  6              5            Jayanti
  </code></pre>
  
  <p>I want to get all the employees that directly or indirectly reports to Sumanta or emp_id(1), and with hierarchy level, like this:</p>
  
  <pre><code>emp_id         hierarchy_level         emp_name
  ------         ---------------        ----------
  2                    1                  Arpita
  4                    1                  Sujon
  5                    2                  Arpan
  6                    3                 Jayanti
  </code></pre>
  
  <p>I am new to SQL and just couldn't find what to use or how to get those results. Is it worth a stored procedure with table valued variable, or just a Tsql select query will be enough. Any help is most welcome.</p>
  
  <p>All I have done is-</p>
  
  <pre><code>Select Ep.emp_id,ep.emp_eame 
  From Emp as E 
  Inner Join Emp as Ep on Ep.reports_to=E.Emp_id 
  Where E.reports_to=1 or E.emp_id=1;
  </code></pre>
  
  <p>but this is accurate upto 2 level and I cant even generate the hierarchy_level no.
  Any suggestion, idea............ will be most helpfull.........</p>