Drop procedure zbom
Go
Create procedure zbom
@ ITEMCODE char (50)
As
The begin
IF the EXISTS (SELECT * FROM sysobjects where TYPE='U' AND name='bommx')
DROP TABLE bommx
The create table bommx
(
Ph1 varchar (50),
Ph2 varchar (50),
Xh varchar (5),
Qty varchar (10)
)
Insert into bommx (ph1 and ph2.
Qty, xh)
SELECT what ZJ had, ZZ, YL, XH FROM
(
The select I.I TEM_CODE what ZJ had, E.I TEM_CODE ZZ, QTY_PER YL,
BD. SequenceNumber XH from BOM B LEFT JOIN BOM_D BD ON B.B OM_ID=BD. BOM_ID
LEFT the JOIN the ITEM I ON I.I TEM_BUSINESS_ID=B.I TEM_ID
LEFT the JOIN ITEM E ON E.I TEM_BUSINESS_ID=BD. SOURCE_ID_ROid
K;
WITH CTE AS
(
The SELECT ph1 and ph2, xh, qty
The FROM bommx BM where ph1=@ ITEMCODE
Union all
The SELECT BM. * FROM bommx BM inner JOIN CTE on CTE. Ph2=BM. Ph1
) select * from CTE
end