The outer stored procedure gives me the product name and the raw material required for it and inner stored procedure gives me the quantity of stored procedure in my warehouse .(for eg: when I search for chocolate(i.e name_grey) in outer stored procedure I get columns with the raw material use to make chocolate(i.e )milk and sugar. Now I want to put milk and sugar as input of inner stored procedure. (where yarnstock1 is my inner stored procedure) How do i do that?

I tried to call complete inner stored procedure in outer stored procedure. Here when i give input chocolate to outer stored procedure i get appropriate column details and i get detail of all raw material which i don't want.

---------------------------------whole stored procedure ---------------------

CREATE DEFINER=`root`@`localhost` PROCEDURE `finalgrey`(IN name_grey varchar(250))
BEGIN
SELECT 
    orderDate, 
productname,
warpyarnid,
warpyarn , 
sum(warpneeded * meters_left) as warpneeded ,
weftyarnid, 
weftyarn,
sum(weftneeded * meters_left) as weftneeded,
    sum(order_quantity), sum(invoice_quantity), sum(meters_left)
FROM
    (SELECT 
    s.orderDate,s.orderToDate,
    s.orderid AS saudaid,
    c.orderId AS challanid,
    pm.productname,
    qc.warpyarnid,
    pwarp.productname AS warpyarn,
    (warperwt / 100)  AS warpneeded,
    qc.weftyarnid,
    pweft.productname AS weftyarn,
    (weftwt / 100)  AS weftneeded, 
    (sum(s.totalQuantity)/count(i.invoiceId)) AS order_quantity,
    sum(i.totalQuantity) AS invoice_quantity,
 IF(((sum(s.totalQuantity)/count(i.invoiceId)) - SUM(i.totalQuantity)) <0, "0", ((sum(s.totalQuantity)/count(i.invoiceId)) - SUM(i.totalQuantity)) ) as meters_left
FROM
    sauda AS s
     LEFT JOIN
    challan c ON s.orderid = c.orderId
     LEFT JOIN
    invoice AS i ON c.challanid = i.challanid
    LEFT JOIN
    productmaster pm ON pm.productmasterid = s.itemId
        LEFT JOIN
    qualitymaster q ON q.productid = pm.productmasterid
        LEFT JOIN
    qualityconfig qc ON qc.qualityid = q.qualitymasterid and qc.active = 0
        LEFT JOIN
    productmaster pweft ON pweft.productmasterid = qc.weftyarnid
        LEFT JOIN
    productmaster pwarp ON pwarp.productmasterid = qc.warpyarnid
WHERE 
    s.active = 0 AND i.active = 0
        AND c.active = 0
        AND s.orderDate >= '2018-04-01' and s.orderType = 'Sales'  and orderToDate >= CURDATE() 
  group by s.orderid ) xcx
where productname LIKE CONCAT('%', name_grey , '%') 
group by productname;
call yarnstock1('');
END
---------------------------------inner stored procedure-----------------------
CREATE DEFINER=`root`@`localhost` PROCEDURE `yarnstock1`(in warp_yarn varchar(250))
BEGIN
select  sum(itna_aayega) , itemId ,productmasterid,productname,sum(itna_bacha) 
from
(select s.orderId,s.orderDate,s.orderToDate,s.itemId,py.purchaseyarnid, iys.inwardyarnstockid,
iys.purchaseorderid,(s.totalQuantity),sum(iys.totalkgs) as itna_aaya, pm.productmasterid,pm.productname,
CASE WHEN orderToDate<current_date() THEN "0"
 WHEN orderToDate>current_date() AND s.totalQuantity < sum(iys.totalkgs) THEN "0"
 ELSE s.totalQuantity - sum(iys.totalkgs)  END AS itna_aayega,
if(orderToDate<current_date(),"0", (sum(iys.finalkgs) * 1)) as itna_bacha #group_concat(iys.totalkgs)
from sauda s
LEFT JOIN  purchaseyarn py 
ON s.orderId = py.purchaseorderid
LEFT JOIN inwardyarnstock iys
on py.purchaseyarnid = iys.purchaseyarnid
left join productmaster pm on s.itemId = pm.productmasterid 
where   iys.active =0 and s.active =0 and py.active = 0  and s.orderDate >= '2018-04-01' and s.type = 'yarn' and s.orderType = 'purchase'
group by s.orderId) xxx
where productname LIKE CONCAT( warp_yarn, '%') 
group by itemId ;
END[]

1 Answers

0
slaakso On

If you want to pass / receive table structure to / from an another stored procedure, you can use a temporary table. A temporary table is visible throughout the connection and a temporary table created in a stored procedure is visible to other stored procedures within same connection.