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 to share