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

Question

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 SP result into a temp table of main SP?

I have created a procedure2(store procedure) that will return a table. Then I have created a procedure1(store 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 Temporary table in procedure1.Whether I have any way for that?

Answer 1

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.