MySQL stored function SELECT INTO unexpected results

Question

I am trying to write a stored function in mysql 5.1 that returns the value 'AccountIDref' for a given room. If I only query the inner SELECT statement this works (returns the value for room). But invoking the function I get the response:

'#1172 - Result consisted of more than one row'

CREATE FUNCTION getAccountId (room INT) RETURNS INT
BEGIN
    DECLARE refID INT DEFAULT NULL;
    SELECT AccountIDref INTO refID FROM Allocation
    WHERE Room = room;
    RETURN refID;
END

What am I doing wrong here?

ah forgot: example is simplified but there should not be more than one row in this case. I have the impression, the WHERE clause is just ignored somehow... — Alex
– Alex, Commented Feb 14, 2013 at 22:52

Devart · Accepted Answer · 2013-02-15 07:12:59Z

1

Field name and parameter name must be different -

CREATE FUNCTION getAccountId (room_param INT) RETURNS INT
BEGIN
    DECLARE refID INT DEFAULT NULL;
    SELECT AccountIDref INTO refID FROM Allocation
    WHERE Room = room_param;
    RETURN refID;
END

In your function you were getting all tables records.

answered Feb 15, 2013 at 7:12

Devart

120k23 gold badges172 silver badges189 bronze badges

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

Comments

Amar · Accepted Answer · 2013-02-14 23:09:54Z

0

What I am going to suggest isn't going to be much different from what you have, but I am skeptical about the where clause being in the next line and also let's use limit 1 to explicitly set the limit.

Try this :

CREATE FUNCTION getAccountId (room INT) RETURNS INT
BEGIN
    DECLARE refID INT DEFAULT NULL;
    SELECT AccountIDref INTO refID FROM Allocation WHERE Room = room LIMIT 1;
    RETURN refID;
END

answered Feb 14, 2013 at 23:09

Amar

12k5 gold badges52 silver badges73 bronze badges

1 Comment

Alex Over a year ago

Meeeh did not help. Now I get the ID of the first table entry as result.

Collectives™ on Stack Overflow

MySQL stored function SELECT INTO unexpected results

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related