SQL Server tree structure recursion (upward recursion and downward recursion)

--Get the current and following department create proc Getcurrentandunderorg@orgid intasbegin     with    CTE        as (SELECT *, 0 as level from Static_organ  WHERE [email protected]        UNION all        SELECT g.*,level+1 from Static_organ G INNER JOIN CTE on              g . Parentorgan=cte. Organid    )    SELECT * from Cteend

　　

--upward recursion   with CTE    as (        SELECT *, levelindex as level from el_departments  WHERE id=170        UNION all        SELECT G.*,g.levelindex as level from El_departments G INNER JOIN CTE on              g.id=cte. ParentID    )

Recursion upward and downward recursively, in fact, is a counter-reverse. The above statement is to use with AS, so the database should be sqlserver2005 above.

SQL Server tree structure recursion (upward recursion and downward recursion)