Which is the equivalent of CONNECT BY PRIOR (from ORACLE) in SQL Server

The following hierarchical query uses the CONNECT BY clause to define the relationship between employees and managers:

(The following code is in ORACLE)
SELECT employee_id, last_name, manager_id, Level
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

----------- ------------------------- ---------- ---------- -------------
        101        Kochhar                          100                  1
        108        Greenberg                      101                   2
        109        Faviet                            108                   3
        110        Chen                             108                   3
        111        Sciarra                          108                   3
        112        Urman                           108                   3
        113        Popp                             108                   3

I need to know what is the equivalent of CONNECT BY PRIOR in SQL Server, or how can I cross a hierarchy in SQL Server?
Who is Participating?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what version of SQL Server?
  in sql 2005, you have to use the Common Table Expression (WITH CTE)
  in sql 2000, you have to develop a procedure/function to simulate it
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.