How to get values from another stored procedure and insert it into the temporary table in one stored procedure in MySQL?

CREATE PROCEDURE `procedure1` ()
BEGIN
    create temporary table temp;
    insert into temp (call procedure2(param1, param2, param3));
END

CREATE PROCEDURE `procedure2` (param1, param2, param3)
BEGIN
    create temporary table temp1
    with recursive cte (id, parent_id, node_level) as (select

query using param1,param2,param3); select * from temp1; END

Above is the situation I am facing, Whether we have any way to insert another stored procedure's result into a temp table of main stored procedure?

I have created a procedure2 (stored procedure) that will return a table. Then I have created a procedure1 (stored procedure) that execute the procedure2 inside itself and after executing procedure2 will return a table with number of rows.

I need to get the table returned from the procedure2 and insert into a temporary table in procedure1. Whether I have any way for that?

Solution

CREATE PROCEDURE `procedure1` ()
BEGIN
create temporary table temp(column1, column 2);
create temporary table temp1(column1, column 2);
WHILE (CONDITION) DO
call procedure2(param1,param2,param3);
insert into temp (select * from temp1);
TRUNCATE temp1;
END WHILE;
select * from temp;
drop table temp1;
drop table temp;
END




CREATE PROCEDURE `procedure2` (param1,param2,param3)
BEGIN
create temporary table temp2
with recursive cte (id, parent_id, node_level) as (select query using param1,param2,param3);
Insert into temp1(select * from temp2);
DROP TABLE temp2;
END

Here I need to execute a piece of code in while loop for that I had added to proc2. Before calling that proc2 in proc1 we need to create one temporary tables(temp) and since it is created before calling the table will remain for proc2. We need to insert the result table of proc2 into the temp and we can now access the table from proc1 after the execution of proc2 completed.

In my case it is a while loop, so I had created two temporary tables (temp,temp1) and I use temp1 as my temporary table to get data from proc2 and then insert into temp (select * from temp1) and then truncate the values of temp1 and the loop goes on till the condition fails and I get the result table temp and I can use data for the proc1

If you have any doubts let me know, thank you.