id    parent_id    name
0    0    All
1    0    Financle Dept
2    0    Admin Dept
3    0    Business Dept
4    0    Serveice Dept
5    4    Sale Dept
6    4    MIS
7    6    UI
8    6    Soft Development
9    8    Inner Development



with testTree (id, parent_id, name, deptLevel) as
(
    select id, parent_id, name, 0 from Dept where id = 9
    union all
    select a.id, a.parent_id, a.name, b.deptLevel+1 from Dept a
        inner join testTree b
    on a.id = b.parent_id
)

select * from testTree

with testTree (id, parent_id, name, deptLevel) as
(
    select id, parent_id, name, 0 from Dept where id = 9
    union all
    select a.id, a.parent_id, a.name, b.deptLevel+1 from Dept a
        inner join testTree b
    on a.id = b.parent_id
)

select * from testTree

OPTION(MAXRECURSION 0)

如果确认你的语句结构没问题,可以后面加个无层次限制的选项

------解决方案--------------------
with testTree (id, parent_id, name, deptLevel) as
(
   select id, parent_id, name, 0 from Dept where id = 9
   union all
   select a.id, a.parent_id, a.name, b.deptLevel+1 from Dept a
       inner join testTree b
   on a.id = b.parent_id
)

select * from testTree
(maxrecursion 1000)
通过这里控制嵌套次数
这里的1000就是嵌套循环的次数上限;如果你想取消限制，设置其为0.

------解决方案--------------------
select * from testTree
OPTION (MAXRECURSION 0);


------解决方案--------------------
SQL code
OPTION(MAXRECURSION 0)
用这个设置最大循环数量

0代表无穷

------解决方案--------------------
探讨

可是我希望的结果是：（即按照level来列出记录）


SQL code

id    parent_id    level
0    0    0    
1    0    1
2    0    1
3    0    1    
4    0    1
5    4    2
6    4    2    
7    6    3
8    6    3
9 ……


------解决方案--------------------
去掉这条数据
0    0    All


------解决方案--------------------
SQL code

with testTree (id, parent_id, name, deptLevel) as
(
    select id, parent_id, name, 0 as levl from #Dept where id = 9
    union all
    select a.id, a.parent_id, a.name, b.deptLevel+1 from #Dept a
        inner join testTree b
    on a.id = b.parent_id and a.id!=0
)

select * from testTree 

         id   parent_id name                             deptLevel
----------- ----------- ------------------------------ -----------
          9           8 Inner Development                        0
          8           6 Soft Development                         1
          6           4 MIS                                      2
          4           0 Serveice Dept                            3

(4 行受影响)

------解决方案--------------------
探讨

哦。 我明白了， 记录0 ALL 的parentid 也被我设置成为0了


SQL code


with testTree (id, parent_id, name, deptLevel) as
(
    select id, parent_id, name, 0 from Dept where id = 0
    union all
    select a.id, ……