--生成测试数据
create table BOM(ID VARCHAR(10),ParentID VARCHAR(10),Name VARCHAR(1000))
INSERT INTO BOM select 'D0','-1','服装'
INSERT INTO BOM select 'D1','D0','上衣'
INSERT INTO BOM select 'D2','D0','裤子'
INSERT INTO BOM select 'D3','D1','中山装'
INSERT INTO BOM select 'D4','D3','圆领中山装'
go
create procedure sp_getChile
@TName nvarchar(40),
@IDName nvarchar(40),
@PIDName nvarchar(40),
@ID nvarchar(20)
as
begin
create table #T(ID VARCHAR(10),PID VARCHAR(10),Level INT)
declare @sql nvarchar(4000)
set @s = N'declare @i int set @i = 1'
set @s = @s + N'
insert into #t select '+@IDName+N','+@PIDName+N','+@Name+N'@i from '+@TName+N' where ID='''+@ID+N''''
set @s = @s + N'
while @@rowcount<>0
begin
set @i = @i + 1
insert into #t
select
a.'+@IDName+N',a.'+@PIDName+N',@i
from
'+@TName+N' a,#t b
where
a.'+@PIDName+N'=b.'+@IDName+N and b.Level = @i-1
end'