mysql stored procedure multiple rows return

Question

i Have a problem returning multiple rows in one output

there are multiple user_roleID's returned. The procedure is below:

CREATE PROCEDURE Sample(OUT userName VARCHAR(30))
BEGIN
SELECT user_roleID INTO userName FROM users;
END

i would like to get a list of data when i print the output value (userName)

call Sample1(@emp);
select @emp;

but when i excecute this command i get the following error reported:

1172 - Result consisted of more than one row

Can somebody tell me how to fix this problem?

kind regards

possible duplicate of How to retreive multiple rows from stored procedure in mysql? — Haim Evgi
– Haim Evgi, Commented Feb 21, 2012 at 14:15

Simon at The Access Group · Accepted Answer · 2012-02-22 08:38:04Z

1

If you are looking to create a stored function to return an ID based on a username, for example, then the following would work

CREATE FUNCTION Sample(inUserName VARCHAR(255)) RETURNS INT(11)
BEGIN
    DECLARE returnValue INT;
    SELECT user_roleID INTO returnValue FROM users WHERE userName = inUserName LIMIT 1;
    RETURN returnValue;
END

This would be called using SELECT Sample('SomeUserName') AS userID;

edited Feb 22, 2012 at 8:38

answered Feb 21, 2012 at 14:18

Simon at The Access Group

7,0251 gold badge25 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

thanksalot Over a year ago

thanks the second was where i was looking for. worked fine :)

Ben Over a year ago

You'll never can return more than one row using LIMIT 1? So you'd be forced to make as you mentioned SELECT Sample('SomeUserName') AS userID; but why than using a function? If I'm not wrong this could be solved with SELECT user_roleID FROM users where userName = 'SomeUserName'?

Simon at The Access Group Over a year ago

You can specify to return more rows from LIMIT, however the OP was just trying to get a roleID for a username using a function, which is only able to return a single value, and so the above worked for them. I am not in a position to know why they chose to do it this way though.

Ben · Accepted Answer · 2014-01-13 06:14:01Z

0

You can't store more than one row that way. To return multiple rows you have to define a cursor or return it directly and not storing it locally, if don't manipulate the data:

CREATE FUNCTION Sample(inUserName VARCHAR(255)) RETURNS INT(11)
BEGIN
    RETURN SELECT user_roleID FROM users WHERE userName = inUserName;
END

Why don't use just SQL instead?

answered Jan 13, 2014 at 6:14

Ben

4016 silver badges13 bronze badges

Collectives™ on Stack Overflow

mysql stored procedure multiple rows return

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related