Ryan Ryan - 1 month ago 6
SQL Question

How to input result of first query into second query inside stored procedure?(Possible?)

I am trying to convert some php queries into a stored procedure. I want to get the result of the first one and input into the second one which is then input into the third one. Yeah, its ridiculous.

Below is my php code.

SELECT item_no AS part, item_desc_1 AS descr FROM imitmidx_sql WHERE user_def_fld_2 = '4'


So I take the result of the above query and input into the next query.

SELECT item_no AS component FROM bmprdstr_sql WHERE comp_item_no = '$partno' and user_def_fld = 'X'


I get the above result and input my results into the next query to retrieve all the orders.

Select
RTRIM(oeordhdr_sql.cus_no) AS customer_number,
oeordhdr_sql.ship_via_cd AS shipper,
oeordlin_sql.user_def_fld_5,
oeordlin_sql.item_no AS item_no,
oeordlin_sql.item_desc_1 AS descr1,
oeordlin_sql.loc AS location1,
oeordhdr_sql.bill_to_name AS Bill_to_Name,
FLOOR (oeordlin_sql.qty_ordered) AS Quantity_Ordered,
convert(varchar, oeordlin_sql.request_dt, 107) AS Requested_Date,
convert(varchar, oeordhdr_sql.shipping_dt, 107) AS Ship_Date,
oeordhdr_sql.status AS Status,
oeordlin_sql.ord_no AS order_no,
ship_to_state AS shipstate,
oeordhdr_sql.phone_number AS phone3,
ship_to_addr_3 AS phone1,
ship_to_addr_2 AS phone2,
imitmidx_sql.user_def_fld_2 AS DTC

FROM oeordhdr_sql, oeordlin_sql, imitmidx_sql

WHERE oeordlin_sql.item_no IN ($searchSTR)
AND oeordlin_sql.loc = 'pmd'
AND imitmidx_sql.item_no = oeordlin_sql.item_no
AND oeordhdr_sql.ord_no = oeordlin_sql.ord_no
AND oeordhdr_sql.status IN ('1','4','2','3','5','6')
AND oeordhdr_sql.shipping_dt>=getdate()-21
AND imitmidx_sql.user_def_fld_2 <> '6'
AND RIGHT(oeordhdr_sql.cus_no,4) <> '4018'

ORDER BY oeordhdr_sql.bill_to_name ASC, oeordlin_sql.item_no ASC


This is what I have for my stored procedure so far.

CREATE PROCEDURE spOrderDetails
AS
BEGIN
Declare @searchSTR

SELECT item_no AS part, item_desc_1 AS descr FROM imitmidx_sql WHERE user_def_fld_2 = '4'


SELECT item_no AS component FROM bmprdstr_sql WHERE comp_item_no = '$partno' and user_def_fld = 'X'

Select
RTRIM(oeordhdr_sql.cus_no) AS customer_number,
oeordhdr_sql.ship_via_cd AS shipper,
oeordlin_sql.user_def_fld_5,
oeordlin_sql.item_no AS item_no,
oeordlin_sql.item_desc_1 AS descr1,
oeordlin_sql.loc AS location1,
oeordhdr_sql.bill_to_name AS Bill_to_Name,
FLOOR (oeordlin_sql.qty_ordered) AS Quantity_Ordered,
convert(varchar, oeordlin_sql.request_dt, 107) AS Requested_Date,
convert(varchar, oeordhdr_sql.shipping_dt, 107) AS Ship_Date,
oeordhdr_sql.status AS Status,
oeordlin_sql.ord_no AS order_no,
ship_to_state AS shipstate,
oeordhdr_sql.phone_number AS phone3,
ship_to_addr_3 AS phone1,
ship_to_addr_2 AS phone2,
imitmidx_sql.user_def_fld_2 AS DTC

FROM oeordhdr_sql, oeordlin_sql, imitmidx_sql

WHERE oeordlin_sql.item_no IN ($searchSTR)
AND oeordlin_sql.loc = 'pmd'
AND imitmidx_sql.item_no = oeordlin_sql.item_no
AND oeordhdr_sql.ord_no = oeordlin_sql.ord_no
AND oeordhdr_sql.status IN ('1','2','3','4','5','6')
AND oeordhdr_sql.shipping_dt>=getdate()-21
AND imitmidx_sql.user_def_fld_2 <> '6'
AND RIGHT(oeordhdr_sql.cus_no,4) <> '4018'

ORDER BY oeordhdr_sql.bill_to_name ASC, oeordlin_sql.item_no ASC
END

Answer

Bung the queries into a CTE. Also, this: FROM oeordhdr_sql, oeordlin_sql, imitmidx_sql has been defunct since 2005. Explicit joins are much clearer.

with Q1 as
(
SELECT item_no AS part, item_desc_1 AS descr 
FROM imitmidx_sql 
WHERE user_def_fld_2 = '4'
)
, Q2 as
(
SELECT item_no AS component 
FROM bmprdstr_sql
WHERE comp_item_no in (select part from Q1) 
and user_def_fld = 'X'
)
 Select
                    RTRIM(oeordhdr_sql.cus_no) AS customer_number,
                    oeordhdr_sql.ship_via_cd AS shipper,
                    oeordlin_sql.user_def_fld_5,
                    oeordlin_sql.item_no AS item_no,
                    oeordlin_sql.item_desc_1 AS descr1,
                    oeordlin_sql.loc AS location1,
                    oeordhdr_sql.bill_to_name AS Bill_to_Name,
                    FLOOR (oeordlin_sql.qty_ordered) AS Quantity_Ordered,
                    convert(varchar, oeordlin_sql.request_dt, 107) AS Requested_Date,
                    convert(varchar, oeordhdr_sql.shipping_dt, 107) AS Ship_Date,
                    oeordhdr_sql.status AS Status,
                    oeordlin_sql.ord_no AS order_no,
                    ship_to_state AS shipstate,
                    oeordhdr_sql.phone_number AS phone3,
                    ship_to_addr_3 AS phone1,
                    ship_to_addr_2 AS phone2,
                    imitmidx_sql.user_def_fld_2 AS DTC

FROM  oeordhdr_sql
INNER JOIN oeordlin_sql
   ON oeordhdr_sql.ord_no = oeordlin_sql.ord_no
INNER JOIN imitmidx_sql
   ON imitmidx_sql.item_no = oeordlin_sql.item_no
INNER JOIN Q2
   ON oeordlin_sql.item_no = Q2.component
WHERE oeordlin_sql.loc = 'pmd'
AND oeordhdr_sql.status IN ('1','4','2','3','5','6')
AND oeordhdr_sql.shipping_dt>=getdate()-21
AND imitmidx_sql.user_def_fld_2 <> '6'  
AND RIGHT(oeordhdr_sql.cus_no,4) <> '4018'
ORDER BY oeordhdr_sql.bill_to_name ASC, oeordlin_sql.item_no ASC