化解32层递归限制

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

ifobject_id('Tree','U')isnotnull
droptable[Tree]
go
CREATETABLE[dbo].[Tree](
[ID][bigint]identity,
[Parent]as(ID-1),
[Name]as('Name'+rtrim(ID))
)
go
declare@iint
set@i=35
while@i>0
begin
insert[tree]defaultvalues
set@i=@i-1
end
--生成格式:
/**//*
IDParentName
--------------------------------------------------------------------
10Name1
21Name2
32Name3
43Name4
54Name5
65Name6
76Name7
87Name8
98Name9
109Name10
................................................

................................................
3130Name31
3231Name32
3332Name33
3433Name34
3534Name35

*/
go
ifobject_id('F_BOM','FN')isnotnull
dropfunctionF_BOM
go
createfunctionF_BOM(@IDint)
returnsnvarchar(1000)
as
begin
declare@snvarchar(1000),@Namenvarchar(20)
lab:
set@Name=(selectNamefromTreewhereID=@ID)
select@ID=ParentfromTreewhereID=@ID
if@Nameisnotnull
begin
set@s=@Name+isnull('-'+@s,'')
gotolab
end
return@s
end


go
ifobject_id('F_BOM2','FN')isnotnull
dropfunctionF_BOM2
go
createfunctionF_BOM2(@IDint)
returnsnvarchar(1000)
as
begin
declare@snvarchar(1000)
whileexists(select1fromTreewhereID=@ID)
select@s=Name+isnull('-'+@s,''),@ID=ParentfromTreewhereID=@ID
return@s
end
go

--SQL2005:

ifobject_id('F_BOM3','FN')isnotnull
dropfunctionF_BOM3
go
createfunctionF_BOM3(@IDint)
returnsnvarchar(max)
as
begin
declare@snvarchar(max);
withBOM(ID,Name,parent,lev)
as
(
selectID,cast(Nameasnvarchar(max)),parent,0fromtreewhereID=@ID
unionall
select
a.ID,cast(a.Name+'-'+b.Nameasnvarchar(max)),a.Parent,b.lev+1
from
Treea
join
BOMbona.ID=b.Parent
)
select@s=NamefromBOMwherelev=(selectmax(lev)fromBOM)
option(maxrecursion0)-----------設置遞歸次數0為無限制，默認為100層
return@s
end
go

selectdbo.F_BOM(35)
selectdbo.F_BOM2(35)
selectdbo.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
*/