I used this solution Mysql make a stored procedure from multiple stored procedures
I have 2 stored procedures which I want to make stored procedure with 2 stored procedures in 1 stored procedure.
This is procedure 1
CREATE DEFINER=`brambang`@`%` PROCEDURE `buyer_statistic_monthly`(IN paramdatefrom datetime, IN paramdateto datetime)
BEGIN
CREATE TEMPORARY TABLE temp2 as SELECT
count_PBR as PBR,
count_PUL as PUL,
count_PEX as PEX,
count_ID as pesanan
FROM
(SELECT
(select -- om.createdby, om.quantity, x1.count_
count(distinct om.createdby) as count_pbr
from (select count(xx.count_) as count_
from (select count(createdby) as count_
from order_match where
order_status_Id in (4, 5, 6, 8)
group by createdby having count(createdby) = 1) xx ) x1,
(select createdby from order_match where order_status_id in (4, 5, 6, 8)
group by createdby having count(createdby) = 1) yy, order_match om where yy.createdby = om.createdby and
order_status_id in (4, 5, 6, 8) and
om.createdAt >= paramdatefrom and om.createdAt <= paramdateto
and NOT EXISTS
(select 1 from order_match om2 where om.createdby = om2.createdby
and order_status_id in (4, 5, 6, 8) and om2.createdAt < paramdatefrom)) count_PBR,
(SELECT count(distinct om.createdby) as count_PUL
from (select count(xx.count_) as count_
from (select count(createdby) as count_
from order_match where order_status_id in (4, 5, 6, 8)
group by createdby having count(createdby) > 1 ) xx ) x1,
(select createdby from order_match
where order_status_id in (4, 5, 6, 8)
group by createdby having count(createdby) > 1 ) yy,
order_match om where yy.createdby = om.createdby
and order_status_id in (4, 5, 6, 8) and om.createdAt >= paramdatefrom
and om.createdAt <= paramdateto
and EXISTS (select 1 from order_match om2 where om.createdby = om2.createdby
and order_status_id in (4, 5, 6, 8) and om2.createdAt <= paramdateto)) count_PUL,
(SELECT
count(distinct om.createdby) as count_PEX
from
order_match om
where om.order_status_id in (4,5,6,8)
and om.createdAt <= paramdateto
and om.createdAt >= paramdatefrom
and EXISTS (select 1 from order_match om2
where om.createdby = om2.createdby
and om2.createdAt < paramdatefrom and
om2.order_status_id in (4, 5, 6, 8))) count_PEX,
(SELECT count(id) from order_match where order_status_id in (4, 5, 6, 8) and createdAt between paramdatefrom
AND paramdateto) count_ID) a
;
END
This is procedure 2
CREATE DEFINER=`brambang`@`%` PROCEDURE `TNP_PEMBELI_PERCENTAGE`(IN paramdatefrom datetime, paramdateto datetime)
BEGIN
CREATE TEMPORARY TABLE temp1 as
SELECT
result_a / result_b * 100 AS percentage_PBR,
100 - (result_a / result_b * 100) AS percentage_PEX
FROM
(SELECT
(select -- om.createdby, om.quantity, x1.count_
count(distinct om.createdby) as count_pbr
from (select count(xx.count_) as count_
from (select count(createdby) as count_
from order_match where
order_status_Id in (4, 5, 6, 8)
group by createdby having count(createdby) = 1) xx ) x1,
(select createdby from order_match where order_status_id in (4, 5, 6, 8)
group by createdby having count(createdby) = 1) yy, order_match om where yy.createdby = om.createdby and
order_status_id in (4, 5, 6, 8) and
om.createdAt >= paramdatefrom and om.createdAt <= paramdateto
and NOT EXISTS
(select 1 from order_match om2 where om.createdby = om2.createdby
and order_status_id in (4, 5, 6, 8) and om2.createdAt < paramdatefrom)) result_a,
(SELECT
COUNT(DISTINCT om.createdby) AS count
FROM
(SELECT
COUNT(xx.count_) AS count_
FROM
(SELECT
COUNT(createdby) AS count_
FROM
order_match
WHERE
order_status_Id IN (4 , 5, 6, 8)
GROUP BY createdby) xx) x1, (SELECT
createdby
FROM
order_match
GROUP BY createdby) yy, order_match om
WHERE
yy.createdby = om.createdby
AND order_status_id IN (4 , 5, 6, 8)
AND om.createdAt >= paramdatefrom
AND om.createdAt <= paramdateto) result_b,
(SELECT
COUNT(DISTINCT om.createdby) AS count
FROM
order_match om
WHERE
om.order_status_id IN (4 , 5, 6, 8)
AND om.createdAt <= paramdateto
AND om.createdAt >= paramdatefrom
AND EXISTS( SELECT
1
FROM
order_match om2
WHERE
om.createdby = om2.createdby
AND om2.createdAt < paramdatefrom
AND om2.order_status_id IN (4 , 5, 6, 8))) result_c
) a;
END
based on 2 procedure above, I wrote this stored procedure so that 2 procedure can run in 1 stored procedure:
CREATE DEFINER=`brambang`@`%` PROCEDURE `TES`(IN paramdatefrom datetime, IN paramdateto datetime)
BEGIN
CALL buyer_statistic_monthly(paramdatefrom, paramdateto);
CALL TNP_PEMBELI_PERCENTAGE(paramdatefrom,paramdateto);
CREATE TEMPORARY TABLE master_temp AS (SELECT * FROM temp1) UNION ALL (SELECT * FROM temp2);
END
but I get an error
Error Code: 1222. The used SELECT statements have a different number of columns
I've tried with @vvvv4d solution, but still I get that error.