List:General Discussion« Previous MessageNext Message »
From:Girish Talluru Date:March 13 2013 12:24pm
Subject:How to return resultset from MySQL Stored Procedure using prepared statement?
View as plain text  
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 THENSET @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?*

Thread
How to return resultset from MySQL Stored Procedure using prepared statement?Girish Talluru13 Mar
  • RE: How to return resultset from MySQL Stored Procedure usingprepared statement?Rick James13 Mar