How to retrieve multiple rows from stored procedure in MySQL?

Question

Am trying to fetch out a field through stored procedure and I used following query. I aimed at fetching out multiple rows, but it executes the result successfully only when a single row exists. Or else it returns an error as I mentioned below.

MySQL Query

DELIMITER ;;
DROP PROCEDURE IF EXISTS Sample1;;

CREATE PROCEDURE Sample1(IN lft1 INT, IN rgt1 INT, OUT emp1 VARCHAR(20))
BEGIN
    SELECT p.emp INTO emp1
    FROM personnel p
    WHERE p.lft > lft1
          AND p.rgt < rgt1
    LIMIT 10;
END;;

CALL Sample1(1, 10, @emp);;
SELECT @emp;

Error Message

MySQL said: Documentation
#1172 - Result consisted of more than one row 

---

NOTE

Sample1 - procedure name
emp - selected field from table personnel
lft - use to check the condition, it is also one of the field of table personnel
personnel - table name

Answer 1

The error is not in your procedure. The error is in your query - it returns more then one row, but you cannot set multiple result into scalar value 'emp1'.

You should limit your query so that it returns one row.

---

How to retreive multiple rows from stored procedure in mysql?

• Plan A: Fill another table, it may be a temporary table.
• Plan B: Just execute your SELECT statement without INTO clause from the procedure; then you could read data-set from the application (c#, PHP+mysqli,...)
• Plan C: Do not use the procedure, just execute the SELECT query.

Answer 2

Just had the same question. After a little research I found a solution in the official MySQL documentation:

• Calling Stored Procedures with Statement Objects

It requires MySQL 5.5.3 or higher.

In contrast to the inital stored procedure from @Bala.C it doesn't use an out parameter.

CREATE PROCEDURE get_data ()
BEGIN
   SELECT Code, Name, Population, Continent
   FROM Country
   WHERE Continent = 'Oceania'
         AND Population < 10000;

   SELECT Code, Name, Population, Continent
   FROM Country
   WHERE Continent = 'Europe'
         AND Population < 10000;

   SELECT Code, Name, Population, Continent
   FROM Country
   WHERE Continent = 'North America'
         AND Population < 10000;
END;