How to create a temporary table in recursion without introducing an additional variable into the received values.?

Question

MySQL

The stored procedure works cyclically and selects a tree from subdirectories based on the 1st main directory.
The task is not to write data to the tmp__index table (permanent table), but to generate a temporary table on the fly and write data there, and delete the table after the procedure is completed.

DELIMITER $$
DROP PROCEDURE IF EXISTS `GetAllCats`$$
CREATE PROCEDURE `GetAllCats` (in search_id INT,in zlevel INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE catalog_id INT;
DECLARE catalog_pid INT;
DECLARE catalog_names VARCHAR(255);
DECLARE cur1 CURSOR FOR SELECT * FROM `cats` where `parent`=search_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
IF zlevel<=0 THEN SET max_sp_recursion_depth=10;
ELSE SET max_sp_recursion_depth= ​​zlevel+1; END IF;
OPEN cur1;
WHILE done = 0 DO
FETCH cur1 INTO catalog_id,catalog_names,catalog_pid;
IF NOT done THEN
insert into tmp__index set
id_s=catalog_id,
base=catalog_pid;
call GetAllCats(catalog_id,zlevel-1);
END IF;
END WHILE;
CLOSE cur1;
END$$
DELIMITER ;

Answer 1

If this is a web application, then it will be much easier to pull all the categories and process them already on the application server. It will just be necessary, because of the same principle, to build a tree from a flat array, and the constructed tree can be cached. When processing in the database, various surprises from mysql are possible. If everything needs to be processed in the database, then you can look towards the temporary table.