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?

• Abdul Manaf: Can you please explain what are you doing in line "SELECT COUNT(1) INTO FoundCount FROM information_schema.tables WHERE table_schema = db_Name AND table_name = Project_List_val; " and after that line.
• Girish: I'm just checking whether that particualr table exist in db or not.

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;

• Abdul Manaf: does this works for you ?
• Girish: Yes, Initially I was doing the same but wanted do thing differently.