if object_id(‘temp1’) is not null
drop table temp1
if object_id(‘temp’) is not null
drop table temp
if object_id(‘ltmp’) is not null
drop table ltmp
if object_id(‘ltemp’) is not null
drop table ltemp
declare @aaa table
(
prd_no nvarchar(20),
bom_no nvarchar(20)
)
select prd_no into temp1 from prdt where prd_no like ‘104-005-%’ order by prd_no
select row_number() over(order by prd_no) as rownum,* into temp from temp1
declare @count int;
select @count=count(星号*) from temp
declare @i int=1;
while(@i<=@count)
begin
declare @prd_no nvarchar(20);
select @prd_no=prd_no from temp where rownum=@i
;with
dcbom as
(select prd_no,* from tf_bom where prd_no=rtrim(@prd_no)
union all
select dcbom.prd_no,tf_bom.* from tf_bom,dcbom where tf_bom.prd_no+‘->’=d