CODEWITHSUNDEEP
mysqlstored-procedures
inba
inba

Reputation: 45

How to return multiple rows using Stored Procedure

I am new to Stored Procedure. I have written a query to get multiple rows. But I am getting error like

Error Code: 1172. Result consisted of more than one row.

Should I use CURSOR type?

Here my query and table structure.

My table structure is:

+---+------+------+---------+
|id | name | class| section |
+---+------+------+---------+
|1  |abc   |5     | A       |
|2  |cdef  |5     | B       |
|3  |hikl  |5     | A       |
|4  |xyz   |5     | A       |
+---+------+------+---------+

My Stored procedure query is 

CREATE DEFINER=`root`@`localhost` PROCEDURE `mulitiOut`(out namee VARCHAR(50))
BEGIN
select name into @namee from mytable where section = A;
END

How can I return all the name related to section A.

Upvotes: 3

Views: 11120

Answers (2)

vhu
vhu

Reputation: 12788

You can return a result set directly from a procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `mulitiOut`()
BEGIN
 SELECT name FROM mytable WHERE section = A;
END

Upvotes: 5

Devart
Devart

Reputation: 121922

Try GROUP_CONCAT function -

SELECT GROUP_CONCAT(name) INTO @namee FROM mytable WHERE section = A;

As a workaround - create and fill another table -

INSERT INTO temp_table(name) SELECT name FROM mytable WHERE section = A;

Upvotes: 2

Related Questions