Exhibition BOM problem (every time want to delete the stored procedure, and then to create, to achie-CodePudding

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),
Lenvel varchar (10)
)

Insert into bommx (ph1 and ph2.
Qty, xh, lenvel)
SELECT what ZJ had, ZZ, CLL, XH, '1' FROM
(
The select I.I TEM_CODE what ZJ had, E.I TEM_CODE ZZ, QTY_PER YL,
ISSUE_OVERRUN_RATE CLL, 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, lenvel
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

The execute zbom '502 c00018'

CodePudding user response:

Why in the process to create table

 
Create procedure zbom 
@ ITEMCODE char (50) 
As 
The begin 

With bommx as (
SELECT what ZJ had, ZZ, CLL, XH, '1' FROM 
(
The select I.I TEM_CODE what ZJ had, E.I TEM_CODE ZZ, QTY_PER YL, 
ISSUE_OVERRUN_RATE CLL, 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 
), the CTE AS 
(
The SELECT ph1 and ph2, xh, qty, lenvel 
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