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),
lenvel varchar(10)
)
insert into bommx(ph1,ph2,
qty,xh,lenvel)
SELECT ZJ,ZZ,CLL,XH,'1.' FROM
(
select I.ITEM_CODE ZJ,E.ITEM_CODE ZZ,QTY_PER YL,
ISSUE_OVERRUN_RATE CLL,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,lenvel
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
execute zbom '502C00018'
uj5u.com熱心網友回復：
為什么要在程序里創建表
create procedure zbom
@ITEMCODE char(50)
as
begin
with bommx as(
SELECT ZJ,ZZ,CLL,XH,'1.' FROM
(
select I.ITEM_CODE ZJ,E.ITEM_CODE ZZ,QTY_PER YL,
ISSUE_OVERRUN_RATE CLL,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
),CTE AS
(
SELECT ph1,ph2,xh,qty,lenvel
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/236299.html
標籤：應用實例
下一篇：資料匹配問題演算法問題