How to return multiple rows in stored procedure?

I have a procedure that returns multiple rows, but separately. Please take a look at its result:

I causes some issues when I want to fetch the result in the code (backend side). Now I want to create a temporary table and insert all rows inside it and then return that temp table as the result of the stored procedure. How can I do that inside procedure?

Not sure it above idea is a good idea .. that’s the only thing I can probably be useful to merge all rows all in one table as SP’s result.

Here is my current procedure:

DELIMITER $$
CREATE DEFINER=`administrator`@`localhost` PROCEDURE `lending_ewallets_balance_in_merchant`(IN `user_id_param` BIGINT UNSIGNED, IN `business_id_param` INT UNSIGNED)
    NO SQL
BEGIN

DECLARE dossier_id INT;
DECLARE query_string VARCHAR(255) DEFAULT '';
DECLARE cursor_List_isdone BOOLEAN DEFAULT FALSE;

DECLARE user_dossiers CURSOR FOR
Select ld.id, lwp.query_string
FROM lending_users_dossiers ld 
JOIN lending_where_to_pays lwp ON ld.lending_where_to_pay_id = lwp.id
WHERE user_id = user_id_param
  AND (ld.status = 'activated' OR ld.status = 'finished');
  # 'finished' is for loans

DECLARE CONTINUE HANDLER FOR NOT FOUND SET cursor_List_isdone = TRUE;

Open user_dossiers;  

loop_List: LOOP
  FETCH user_dossiers INTO dossier_id, query_string;  
    IF cursor_List_isdone THEN
      LEAVE loop_List;
    END IF;

    SET @qry = CONCAT(
                "SELECT ld.id lending_dossier_id, ld.type, SUM(let.credit) balance
                FROM lending_users_dossiers ld
                JOIN lending_ewallet_transactions let
                ON ld.id = let.lending_dossier_id
                WHERE ld.id = ", dossier_id,
                " AND ", business_id_param, " IN(", query_string, ")",
                "GROUP BY ld.id, ld.type");

    PREPARE stmt FROM @qry;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
END LOOP loop_List;

Close user_dossiers;
END$$
DELIMITER ;

Noted that, the MySQL version I use is MySQL v8.0.20.

Answer

The logic should be something like this. Outside the loop create a temp table if not exists and delete the data from it:

CREATE TEMPORARY TABLE
IF NOT EXISTS
user_dossiers_tmp (your columns);

DELETE FROM user_dossiers_tmp;

In your loop:

INSERT INTO user_dossiers_tmp VALUES (your data);

After your loop:

SELECT * FROM user_dossiers_tmp;
END$$

Advertisement

Answer