CRM定制，軟件開發，辦公OA，沈陽易勢科技最專業
create proc wuxian
(
 @table nvarchar(100),--表名
 @column nvarchar(200),--查詢出的列名
 @parent nvarchar(200),--查詢父級條件
@parentcolumn nvarchar(50),--父級關聯字段
@soncolumn nvarchar(50),--子級關聯字段
@where nvarchar(200)--其他查詢條件
)
as
declare @rows nvarchar(200),@allrows nvarchar(200),@index int
begin
set @rows=''
set @allrows=@column
while(@allrows<>'')
begin
 set @index=case when charindex(',',@allrows)=0 then len(@allrows)+1 else (charindex(',',@allrows)+1) end
 set @rows=@rows+'t.'+left(@allrows,@index-1)
 set @allrows=right(@allrows,len(@allrows)-@index+1)
end
exec('
;with cte
as(
select '+@column+',cast(id as nvarchar(50)) sid,id, 1 as leavel from '+@table+' where '+@parent+'
 union all select  '+@rows+',
cast((cte.sid+cast(t.id as nvarchar(50))) as nvarchar(50)) sid,t.id,(cte.leavel+1) as leavel from '+@table+' t,cte
where t.'+@soncolumn+'=cte.'+@parentcolumn+'
) select '+@column+',leavel from cte '+@where+' order by sid')
end