CODEWITHSUNDEEP
mysqlstored-procedures
Alex
Alex

Reputation: 7838

How do I return multiple rows one at a time from a MySQL stored procedure

I want to return a set that is created dynamically inside a stored procedure (for the sake of simplicity, let's say the set is a list of even numbers that are calculated inside a loop).

Some databases I've worked with had a SUSPEND operation that added the current values of the OUT parameters to the result set of the procedure, like so:

DELIMITER $

CREATE PROCEDURE EvenNumbers(
    IN n INT, OUT NUM INT )
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        SET NUM = i;
        SUSPEND; /* adds a row with a value 'i' for column NUM to the result set */
        SET i = i+2;
    END WHILE;
END$

DELIMITER ;

SUSPEND is a keyword in MySQL but I assume it doesn't do the same thing because this doesn't work (Error: Unexpected 'SUSPEND').

So, how do I return a set of rows one by one from a stored procedure in MySQL?

Upvotes: 0

Views: 1635

Answers (2)

slaakso
slaakso

Reputation: 9070

Alex, you can create a temporary table inside the stored procedure (which is perfectly ok), store the result of the calculation in it and return the content of the temp table once you are done with the calculations. So, instead of the OUT parameters, use normal result set returned by the procedure.

Upvotes: 2

Raymond Nijland
Raymond Nijland

Reputation: 11602

I understand you want a even number generator? This is pure MySQL without making use of stored procedure.

First you need to generate a lot off numbers with the query below. This query generates 1 to 10000 records.

Query

  SELECT 
    @row := @row + 1 AS ROW
  FROM  
  (
     SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) row1
  CROSS JOIN (
     SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) row2
  CROSS JOIN (
     SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) row3
    CROSS JOIN (
     SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) row4  

  CROSS JOIN (
    SELECT @row := 0
  ) init_user_param

The main query.

Query

SELECT
 *
FROM (

  SELECT 
    @row := @row + 1 AS ROW
  FROM  
  (
     SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) row1
  CROSS JOIN (
     SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) row2
  CROSS JOIN (
     SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) row3
    CROSS JOIN (
     SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
  ) row4  

  CROSS JOIN (
    SELECT @row := 0
  ) init_user_param

) 
 ROWS
WHERE
  ROWS.ROW BETWEEN 1 AND 10
 AND
  ROWS.ROW MOD 2 = 0

Result

   row  
--------
       2
       4
       6
       8
      10

Upvotes: 1

Related Questions