IF EXISTS(select * from sysobjects where type='P'AND name='zbom')
drop procedure zbom
go
create procedure zbom
@ITEMCODE char(50)
as
begin
IF EXISTS(SELECT * FROM sysobjects where TYPE='U' AND name='bommx')
DROP TABLE bommx
create table bommx
(
ph1 varchar(50),
ph2 varchar(50),
xh varchar(5),
qty varchar(10)
)
insert into bommx(ph1,ph2,
qty,xh)
SELECT ZJ,ZZ,YL,XH FROM
(
select I.ITEM_CODE ZJ,E.ITEM_CODE ZZ,QTY_PER YL,
BD.SequenceNumber XH from BOM B LEFT JOIN BOM_D BD ON B.BOM_ID=BD.BOM_ID
LEFT JOIN ITEM I ON I.ITEM_BUSINESS_ID=B.ITEM_ID
LEFT JOIN ITEM E ON E.ITEM_BUSINESS_ID=BD.SOURCE_ID_ROid
)K;
WITH CTE AS
(
SELECT ph1,ph2,xh,qty
FROM bommx BM where ph1=@ITEMCODE
union all
SELECT BM.* FROM bommx BM inner JOIN CTE on CTE.ph2=BM.ph1
)select * from CTE
end
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/237361.html
標籤：應用實例
下一篇：怎樣在手機上編程