CODEWITHSUNDEEP
mysql
Bala
Bala

Reputation: 3638

How to retrieve multiple rows from stored procedure in MySQL?

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

Upvotes: 9

Views: 66047

Answers (3)

TNguyen
TNguyen

Reputation: 176

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;;

Upvotes: 7

user3761776
user3761776

Reputation: 209

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;

Upvotes: 10

Devart
Devart

Reputation: 121932

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.

Upvotes: 11

Related Questions