无限级分类查询一个栏目下的所有子栏目

接近通用：（本存储过程有个弊端就是，当你查询的子栏目加起来的字符串的长度大于nvarchar(max)的时候，字符串会自动截取，就会出现有些子栏目查不出来的情况，所有只能查询那些子栏目适中的无限级联）

------------------------------------------------------
---存储过程-无限级联-获得该栏目下的所有子栏目---
------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UP_limitcascade]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[UP_limitcascade]
GO
create procedure UP_limitcascade
@catid int,--栏目ID,
@table nvarchar(50),--表名
@idfield nvarchar(50),--栏目列名
@parentidfield nvarchar(50)--栏目父级列名
as

declare @catstr nvarchar(max)--该栏目下的所有子栏目的id字符串:如(1,2)
declare @tempcatstr nvarchar(max)--中间过度变量1
declare @tempcatstr1 nvarchar(max)--中间过度变量2
declare @catcount int--栏目的子栏目总数
declare @sql nvarchar(max)

set @sql = 'select @catcount=count(*) from '+@table+' where '+@parentidfield+' ='+str(@catid)
exec sp_executesql @sql,N'@catcount int output',@catcount output
set @catstr = cast(@catid as nvarchar)
set @tempcatstr1 = cast(@catid as nvarchar)
set @tempcatstr = ''
while(@catcount>0)
begin
 set @sql='select @catstr=@catstr+'',''+cast('+@idfield+' as nvarchar),@tempcatstr=@tempcatstr
  +'',''+cast('+@idfield+' as nvarchar) from '+@table+' where
  charindex('',''+cast('+@parentidfield+' as nvarchar)+'','','','+@tempcatstr1+','')>0'
 exec sp_executesql @sql,N'@catstr nvarchar(max) output,@tempcatstr nvarchar(max) output',
  @catstr output,@tempcatstr output
 print @tempcatstr+ char(13)+char(10)
 if(len(@tempcatstr)>0)
 begin
  set @tempcatstr1 = right(@tempcatstr,len(@tempcatstr)-1)
 end
 set @sql='select @catcount=count(*) from '+@table+' where
  charindex('',''+cast('+@parentidfield+' as nvarchar)+'','','','+@tempcatstr1+','')>0'
 exec sp_executesql @sql,N'@catcount int output',@catcount output
 set @tempcatstr = ''
end
set @sql='select * from '+@table+' where charindex('',''+cast('+@idfield+
  ' as nvarchar)+'','','','+@catstr+','')>0 order by '+@parentidfield
exec sp_executesql @sql