9

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

Improve this question

3 Answers 3

Reset to default
11

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.
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

+1, Now, The OP question still is How he can retreive multiple rows from his stored procedure ???
@Devart even if i don't give limit..it returns the same error..!
@Bala.C, as @Devart said you have to ensure that your query inside the stored procedure returns only on row you can't return mutiple rows, so Yes it must return the same error, os you have to limit it like where id = 1 or something like that.
@MahmoudGamal if you use devart's plan B you can do that thing you want.
10

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

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;
Improve this answer

2 Comments

The selected answer is good, but it seems to answer a different question. I also needed to know how to output multiple rows from a stored procedure. Thanks for answer the question as it's stated! For others, this is the CORRECT answer for "outputting data from multiple rows using a stored procedure".
When I tried this answer on MySQL 5.7, I only got the result from the first SELECT in the output of the procedure.
7

You can use a cursor in MySQL.

CREATE PROCEDURE Sample1(IN lft1 INT,IN rgt1 INT, OUT emp1 VARCHAR(20))
BEGIN
    DECLARE p_emp INT;
    DECLARE cur_emp CURSOR FOR
        SELECT p.emp
        FROM personnell p
        WHERE p.lft > lft1
              and p.rgt < rgt1
        LIMIT 10;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMoreRow = 0;

    OPEN cur_emp;

    LOOPROWS: LOOP

        IF noMoreRow = 0 THEN
            CLOSE cur_emp;
            LEAVE LOOPROWS;
        END IF;

        FETCH cur_emp INTO p_emp;
        SELECT p_emp;

    END LOOP;
END;;
Improve this answer

1 Comment

This is better answer if you are looking to perform some function on multi-row fetched data.

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.