How to get multiple rows from stored procedure in mysql?

Question

I am trying to retrieve a field through a stored procedure, and I used the following query. I aimed to collect several rows, but it only succeeds when there is one row. Or 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 personnell 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 personnell lft -----use to check the condition,it is also one of the field of table personnell personnell------table name 

Answer 1

The error is not in your procedure. The error in your request is that it returns more than one row, but you cannot set multiple results to the scalar value "emp1".

You must limit your query so that it returns a single row.

---

How to recover multiple rows from stored procedure in mysql?

• Plan A. Fill in another table, it may be a temporary table.
• Plan B : just execute the SELECT statement without the INTO clause from the procedure; then you can read the dataset from the application (C #, PHP + mysqli, ...)
• Plan C. Do not use this procedure, just execute a SELECT query.

Answer 2

I had the same question. After a little research, I found a solution in the official documentation. http://dev.mysql.com/doc/connector-cpp/en/connector-cpp-tutorials-stored-routines-statements.html It requires MySQL 5.5.3 or higher.

Unlike the built-in stored procedure from @ Bala.C, it does not use the 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; 

Answer 3

You can use 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;;