前一镇，写了两个递归的树型结构处理函数：
http://expert.csdn.net/Expert/topic/1343/1343007.xml?temp=.730694
后来有朋友提出32层以上嵌套的一个展BOM的实例，没有办法用递归实现，特想了个办法，实现32层以上树型结构的递归方法。现在特将以前的那个函数进行了改进，具体如下：
---------------------------------表及函数脚本
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TreeClass]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TreeClass]
GO
CREATE TABLE [dbo].[TreeClass] (
[TC_id] [int] IDENTITY (1, 1) NOT NULL ,
[TC_PID] [int] NOT NULL ,
[TC_OtherTypeID] [varchar] (8000) COLLATE Chinese_PRC_CI_AS NULL ,
[TC_Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TreeClass] WITH NOCHECK ADD
CONSTRAINT [PK_TreeClass] PRIMARY KEY CLUSTERED
(
[TC_id]
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FN_32GetSubClass]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FN_32GetSubClass]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FN_32GetTopClass]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FN_32GetTopClass]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION FN_32GetSubClass (@InputId int,@IdStr varchar(8000)='',@LevelCount int=-1)
/*
参数： @InputId，被搜索子类的ID
@IdStr，一个特殊参数，用于在递归中传数据，注意：调用函数时一定要传入‘’空值
@LevelCount 用于判断是不是递归调用的开始层
*/
RETURNS Varchar(8000)
AS
BEGIN
Declare @TC_ID int,@TC_PID int,@StartLevel int,@Id32 int
if @LevelCount=-1
begin
set @StartLevel=@@NESTLEVEL
set @LevelCount=@StartLevel
end
else
set @StartLevel=-1
If @IdStr='' Set @IdStr=''''+cast(@InputId as varchar)+''''
DECLARE TreeClass CURSOR local FOR --定义游标
SELECT TC_Id,TC_PID
FROM TreeClass
where TC_PID=@InputId
OPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @TC_ID,@TC_PID
WHILE @@FETCH_STATUS = 0 --循环游标，即循环当前类的弟一级子类
BEGIN
select @IdStr=@IdStr+','+''''+cast(@tC_ID as varchar)+''''
if @@NESTLEVEL<32
set @IdStr=dbo.FN_32GetSubClass (@TC_ID,@IdStr,@LevelCount) --递归，自己调用自己。
else
set @IdStr='['+cast(@tC_ID as varchar)+']'+@IdStr
FETCH NEXT FROM TreeClass
INTO @tC_ID,@TC_PID
End
CLOSE TreeClass
DEALLOCATE TreeClass
while @StartLevel=@@NESTLEVEL and charindex(']',@IdStr)>0
begin
set @Id32=substring(@IdStr,2,charindex(']',@IdStr)-2)
set @IdStr=dbo.FN_32GetSubClass (@Id32,@IdStr,@LevelCount)
set @IdStr=replace(@IdStr,'['+cast(@Id32 as varchar)+']','')
end
Return @IdStr
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION FN_32GetTopClass (@InputId int,@IdStr varchar(8000)='',@type int=0,@LevelCount int=-1)
RETURNS Varchar(8000)
AS
BEGIN
Declare @TC_ID int,@TC_PID int,@StartLevel int,@Id32 int
if @LevelCount=-1
begin
set @StartLevel=@@NESTLEVEL
set @LevelCount=@StartLevel
end
else
set @StartLevel=-1
DECLARE TreeClass CURSOR local FOR
SELECT TC_Id,TC_PID
FROM TreeClass
where TC_ID=@InputId
OPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @TC_ID,@TC_PID
WHILE @@FETCH_STATUS = 0
BEGIN
if @type=1
begin
if @IdStr<>'' select @IdStr=','+@IdStr
select @IdStr=''''+cast(@tC_ID as varchar)+''''+@IdStr
end
else
if @TC_PID=0 select @IdStr=cast(@tC_ID as varchar)
if @@NESTLEVEL<32
select @IdStr=dbo.FN_32GetTopClass (@TC_PID,@IdStr,@type,@LevelCount)
else
set @IdStr=@IdStr+'['+cast(@tC_ID as varchar)+']'
FETCH NEXT FROM TreeClass
INTO @tC_ID,@TC_PID
End
CLOSE TreeClass
DEALLOCATE TreeClass
while @StartLevel=@@NESTLEVEL and charindex(']',@IdStr)>0
begin
set @Id32=substring(@IdStr,charindex('[',@Idstr)+1,charindex(']',@IdStr)-1-charindex('[',@Idstr))
set @IdStr=dbo.FN_32GetTopClass (@Id32,@IdStr,@type,@LevelCount)
set @IdStr=replace(@IdStr,'['+cast(@Id32 as varchar)+']','')
end
Return @IdStr
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET
---------------------------------------------------------------
收藏先，下午有时间在看。
---------------------------------------------------------------
CrazyFor (蚂蚁)
这种致富不忘穷兄弟的做法值得鼓励！
---------------------------------------------------------------
学习！
---------------------------------------------------------------
http://expert.csdn.net/Expert/TopicView2.asp?id=962569&datebasetype=now
BuildRelation:(555,里面几个递归来重建ItemsRelation,但是我的网站进不去了。。)
http://www.lostinet.com/temp/ItemsRelation.sql