接近通用：（本存储过程有个弊端就是，当你查询的子栏目加起来的字符串的长度大于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