How to return resultset from MySQL Stored Procedure using prepared statement?

Question

DELIMITER $$

CREATE PROCEDURE List_IL()

BEGIN

  DECLARE Project_Number_val VARCHAR( 255 );
  DECLARE Temp_List_val VARCHAR(255);
  DECLARE Project_List_val VARCHAR(255);
  DECLARE FoundCount INT;
  DECLARE Project_Number INT;
  DECLARE db_Name VARCHAR(255);



  DECLARE no_more_rows BOOLEAN;
  DECLARE loop_cntr INT DEFAULT 0;
  DECLARE num_rows INT DEFAULT 0;


  DECLARE   projects_cur    CURSOR FOR
    SELECT  Project_Id
    FROM    Project_Details;


  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;


  OPEN projects_cur;
  select FOUND_ROWS() into num_rows;

  the_loop: LOOP

    FETCH  projects_cur
    INTO   Project_Number_val;


    IF no_more_rows THEN
        CLOSE projects_cur;
        LEAVE the_loop;
    END IF;


SET Project_List_val = CONCAT(Project_Number_val, '_List');
SET db_Name='panel';


SELECT COUNT(1) INTO  FoundCount  FROM information_schema.tables WHERE table_schema = `db_Name`  AND table_name = `Project_List_val`;

 IF FoundCount = 1 THEN
SET @Project_Number=Project_Number_val;

SET @sql = CONCAT(' SELECT Panel_Id,', Project_Number_val,'
                    FROM ', @Project_List_val,' Where status=1');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;

    SET loop_cntr = loop_cntr + 1;
  END LOOP the_loop;


END $$

In the above stored procedure How can I get the all the rows selected during execution of prepared statement and after the loop terminates I want to return the entire result set whichever calls the stored procedure. Can you please help me how to do this?

Answer 1

I will suggest you a solution create a log table like this

CREATE TABLE log_records
(
Panel_Id INT,
Project_Number_val VARCHAR(255)
);

In your code add lines as follows here i will insert those record into log_table which you was selecting please look the line /**Added by abdul */

    IF FoundCount = 1 THEN
        SET @Project_Number=Project_Number_val;

        SET @sql = CONCAT(' SELECT Panel_Id,', Project_Number_val,'    FROM ', @Project_List_val,' Where status=1');


    /*--Added by Abdul*/
        SET @insert_sql = CONCAT ('INSERT INTO log_records' ,'SELECT Panel_Id,', Project_Number_val,'    FROM ', @Project_List_val,' Where status=1');
        PREPARE stmt_insert FROM @insert_sql;
        EXECUTE stmt_insert;
        DEALLOCATE PREPARE stmt_insert; 
    /**--End**/     

        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

And then where loop end add line

SELECT * FROM log_records;

This will give you the desired result.

Also Write one line in your code before you open the cursor otherwise each procedure call will be appending data in that table.

TRUNCATE TABLE log_table;