2
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?

2
  • 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. Mar 13, 2013 at 12:11
  • I'm just checking whether that particualr table exist in db or not.
    – Girish
    Mar 13, 2013 at 12:12

1 Answer 1

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;
2
  • does this works for you ? Mar 13, 2013 at 12:28
  • Yes, Initially I was doing the same but wanted do thing differently.
    – Girish
    Mar 13, 2013 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.