List:General Discussion« Previous MessageNext Message »
From:Rick James Date:March 13 2013 4:50pm
Subject:RE: How to return resultset from MySQL Stored Procedure using
prepared statement?
View as plain text  
What language are you using?
In Perl, there is
   $sth->more_results;

> -----Original Message-----
> From: Girish Talluru [mailto:girish.dev1986@stripped]
> Sent: Wednesday, March 13, 2013 5:24 AM
> To: mysql@stripped
> Subject: How to return resultset from MySQL Stored Procedure using
> prepared statement?
> 
> 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