MySQL stored procedure, multiple SELECTs only return one row?

I was working on a stored procedure that runs a select statement in a loop.

When viewing the results via mysqli or phpmyadmin, I only get one row. What do I need to do to return multiple rows?

Here's a really simple example to illustrate my problem ....

DROP PROCEDURE IF EXISTS simple //
CREATE PROCEDURE simple()
BEGIN

DECLARE c INT(10);
SET c = 1;

REPEAT

  SELECT c;
  SET c = c + 1;

UNTIL c >= 10 END REPEAT;

END //

      

+3


source to share


1 answer


I think the best way to deal with this is to actually store your output in a temporary table and then make the final selection at the end of the while loop.



DROP PROCEDURE IF EXISTS simple //
CREATE PROCEDURE simple()
BEGIN

    CREATE TEMPORARY TABLE output (finalC INT(10));

    DECLARE c INT(10);
    SET c = 1;

    REPEAT

      INSERT INTO output SELECT c;
      SET c = c + 1;

    UNTIL c >= 10 END REPEAT;

    SELECT * FROM output;

END //

      

+1


source







All Articles