Create Table Mrp_bomsub( Mbno varchar(5),SMno varchar(5), Amount int) insert into Mrp_bomsub select 'A', 'B', 1 union all select 'A' , 'C', 2 union all select 'A' , 'D', 1 union all select 'A1', 'B', 1 union all select 'A1', 'D', 1 union all select 'A1', 'F', 3 union all select 'A1', 'I', 1 union all select 'C' , 'E', 2 union all select 'C' , 'F', 1 union all select 'E' , 'G', 2 union all select 'E' , 'H', 1 union all select 'G' , 'O', 1 union all select 'I' , 'J', 2 union all select 'I' , 'L', 1 union all select 'J' , 'E', 1 union all select 'J' , 'M', 1 union all select 'G' , 'P', 2 ----------------------------------------------------------------------------------------- --------------第一個存儲過程--------------------------------- Create proc SB @smno varchar(5) as declare @mbno varchar(5),@b varchar(200) set @b='' Declare kk cursor local for select smno from Mrp_bomsub where mbno=@smno open kk fetch next from kk into @mbno while @@fetch_status=0 begin if exists(select * from Bb where rtrim(left(reverse(b),charindex(',',reverse(b))))=@smno+',' ) begin select @b=b from Bb where rtrim(left(reverse(b),charindex(',',reverse(b))))=@smno+',' insert into Bb select @b+','+@mbno end else begin insert into Bb select @smno+','+@mbno end exec SB @mbno fetch next from kk into @mbno end close kk deallocate kk ---------------過程結束-------------------- -------------第二個存儲過程--------------- Create Proc SB1 @smno varchar(5) as Create Table Bb(b varchar(200)) Truncate Table Bb exec SB @smno select replace(B,',',' ') from Bb order by len(B) drop table Bb ---------------過程結束---------------- exec SB1 'A' ---------------------執行過程 --------------結果---------------------- A B A C A D A C E A C F A C E H A C E G A C E G O A C E G P ----------------------------------------------- exec SB1 'A1' ---------------------執行過程 ---------------結果------------------------------ A1 B A1 D A1 F A1 I A1 I J A1 I L A1 I J M A1 I J E A1 I J E G A1 I J E H A1 I J E G P A1 I J E G O drop proc sb -----------刪除過程1 drop proc sb1 -------刪除測試過程2
Create Table Mrp_bomsub( Mbno varchar(5),SMno varchar(5), Amount int)
insert into Mrp_bomsub
select 'A', 'B', 1 union all
select 'A' , 'C', 2 union all
select 'A' , 'D', 1 union all
select 'A1', 'B', 1 union all
select 'A1', 'D', 1 union all
select 'A1', 'F', 3 union all
select 'A1', 'I', 1 union all
select 'C' , 'E', 2 union all
select 'C' , 'F', 1 union all
select 'E' , 'G', 2 union all
select 'E' , 'H', 1 union all
select 'G' , 'O', 1 union all
select 'I' , 'J', 2 union all
select 'I' , 'L', 1 union all
select 'J' , 'E', 1 union all
select 'J' , 'M', 1 union all
select 'G' , 'P', 2
-----------------------------------------------------------------------------------------
--------------第一個存儲過程---------------------------------
Create proc SB
@smno varchar(5)
as
declare @mbno varchar(5),@b varchar(200)
set @b=''
Declare kk cursor local for
select smno from Mrp_bomsub where mbno=@smno
open kk
fetch next from kk into @mbno
while @@fetch_status=0
begin
if exists(select * from Bb where rtrim(left(reverse(b),charindex(',',reverse(b))))=@smno+',' )
begin
select @b=b from Bb where rtrim(left(reverse(b),charindex(',',reverse(b))))=@smno+','
insert into Bb
select @b+','+@mbno
end
else
begin
insert into Bb
select @smno+','+@mbno
end
exec SB @mbno
fetch next from kk into @mbno
end
close kk
deallocate kk
---------------過程結束--------------------
-------------第二個存儲過程---------------
Create Proc SB1
@smno varchar(5)
as
Create Table Bb(b varchar(200))
Truncate Table Bb
exec SB @smno
select replace(B,',',' ') from Bb order by len(B)
drop table Bb
---------------過程結束----------------
exec SB1 'A' ---------------------執行過程
--------------結果----------------------
A B
A C
A D
A C E
A C F
A C E H
A C E G
A C E G O
A C E G P
-----------------------------------------------
exec SB1 'A1' ---------------------執行過程
---------------結果------------------------------
A1 B
A1 D
A1 F
A1 I
A1 I J
A1 I L
A1 I J M
A1 I J E
A1 I J E G
A1 I J E H
A1 I J E G P
A1 I J E G O
drop proc sb -----------刪除過程1
drop proc sb1 -------刪除測試過程2