CODEWITHSUNDEEP
mysqlfunction
吴环宇
吴环宇

Reputation: 21

Call a stored function in mysql,But get an error:Subquery returns more than 1 row

In mysql database,I have a table called 'usertest' which has some user information stored in it. And I create a UDF called 'getId' successfully. This is how it created: 

create function getId(name varchar(255))
returns int
begin
declare id_found int;
set id_found=(select Id from usertest where Name=name);
return id_found;
end

It should be OK.However,when I call 

select getId('mike');

mysql engine reports an error:

Subquery returns more than 1 row.

But in table usertest I have only one row where Name='mike'.That's wired.Someone can tell me why? Thank you in advance :)

Upvotes: 1

Views: 293

Answers (1)

Barmar
Barmar

Reputation: 781088

Column names and variables are case-insensitive, so Name and name are the same thing. It's not comparing the Name column with the name variable, it's comparing the name variable with itself, so it's always true.

Use a different name for the function parameter.

create function getId(name_param varchar(255))
returns int
begin
declare id_found int;
set id_found=(select Id from usertest where Name=name_param);
return id_found;
end

or use a table name prefix.

create function getId(name varchar(255))
returns int
begin
declare id_found int;
set id_found=(select Id from usertest where usertest.Name=name);
return id_found;
end

See MySQL : When stored procedure parameter name is the same as table column name

Upvotes: 3

Related Questions