化解32层递归限制

-- 2005化解32层递归限制,2000可用循环替代或用(游标while加break递归自己用一个变量传参)

if   object_id ( ' Tree ' , ' U ' )  is   not   null
     drop   table   [ Tree ]
go
  CREATE   TABLE   [ dbo ] . [ Tree ] (
     [ ID ]   [ bigint ]   identity ,
     [ Parent ]   as  (ID - 1 ),
     [ Name ]   as  ( ' Name ' + rtrim (ID))
) 
go
declare   @i   int
set   @i = 35
while   @i > 0
begin
     insert   [ tree ]   default   values
     set   @i = @i - 1
end
-- 生成格式:
/**/ /*
ID                   Parent               Name
-------------------- -------------------- ----------------------------
1                    0                    Name1
2                    1                    Name2
3                    2                    Name3
4                    3                    Name4
5                    4                    Name5
6                    5                    Name6
7                    6                    Name7
8                    7                    Name8
9                    8                    Name9
10                   9                    Name10
................................................

................................................
31                   30                   Name31
32                   31                   Name32
33                   32                   Name33
34                   33                   Name34
35                   34                   Name35

*/
go
if   object_id ( ' F_BOM ' , ' FN ' )  is   not   null
     drop   function  F_BOM
go
create   function  F_BOM( @ID   int )
returns   nvarchar ( 1000 )
as
begin
     declare   @s   nvarchar ( 1000 ), @Name   nvarchar ( 20 )
    lab:
     set   @Name   = ( select  Name  from  Tree  where  ID = @ID )
     select   @ID = Parent  from  Tree  where  ID = @ID
     if   @Name   is   not   null
         begin
             set   @s = @Name + isnull ( ' - ' + @s , '' )
             goto  lab
         end
     return   @s
end


go
if   object_id ( ' F_BOM2 ' , ' FN ' )  is   not   null
     drop   function  F_BOM2
go
create   function  F_BOM2( @ID   int )
returns   nvarchar ( 1000 )
as
begin
     declare   @s   nvarchar ( 1000 )
     while   exists ( select   1   from  Tree  where  ID = @ID )
         select   @s = Name + isnull ( ' - ' + @s , '' ), @ID = Parent  from  Tree  where  ID = @ID
     return   @s
end
go

-- SQL2005:

if   object_id ( ' F_BOM3 ' , ' FN ' )  is   not   null
     drop   function  F_BOM3
go
create   function  F_BOM3( @ID   int )
returns   nvarchar ( max )
as
begin
     declare   @s   nvarchar ( max );
     with  BOM(ID,Name,parent,lev)
     as
    (
     select  ID, cast (Name  as   nvarchar ( max )),parent, 0   from  tree  where  ID = @ID
     union   all
     select  
        a.ID, cast (a.Name + ' - ' + b.Name  as   nvarchar ( max )),a.Parent,b.lev + 1
     from  
        Tree a
     join
        BOM b  on  a.ID = b.Parent
    )    
     select   @s = Name  from  BOM  where  lev = ( select   max (lev)  from  BOM)
     option (maxrecursion  0 )                                         -- ---------設置遞歸次數0為無限制，默認為100層
     return   @s
end
go

select  dbo.F_BOM( 35 )
select  dbo.F_BOM2( 35 )
select  dbo.F_BOM3( 35 )
/**/ /*
Name1-Name2-Name3-Name4-Name5-Name6-Name7-Name8-Name9-Name10-
Name11-Name12-Name13-Name14-Name15-Name16-Name17-Name18-Name19-Name20-
Name21-Name22-Name23-Name24-Name25-Name26-Name27-Name28-Name29-Name30-
Name31-Name32-Name33-Name34-Name35
*/