I have just started to create a stored function this is my first time so I am having a few problems. Currently I call the function using SELECT test();
(test is the function name for now). I want to send a number to the function (username ID) and have the username returned.
I have this working by using SELECT test(1);
1 is the ID of a user in the table. This seems to work as the username is returned, but if I type in any number the same username is returned also.
BEGIN
DECLARE new_username VARCHAR(90);
SELECT `username` INTO new_username FROM `users` WHERE `ID` = ID;
return new_username;
END
I have set the paramter as ID int
.
Am I right in thinking that the keyword INTO
will put the value of the username into the variable new_username
? If I run it without the INTO
I get the error:
Not allowed to return a result set from a function
Have I made any obvious mistakes in this, I hope I havent done it totally wrong. Thanks for any advice :).
Edit : I just added a few more rows into my table , I now get the error:
Result consisted of more than one row
Full sql version:
CREATE DEFINER=`elliotts`@`%` FUNCTION `test`(ID int)
RETURNS varchar(32) CHARSET latin1
BEGIN
DECLARE new_username VARCHAR(32);
SELECT `username`
INTO new_username
FROM `users`
WHERE `ID` = ID;
return new_username;
END
Use:
DROP FUNCTION IF EXISTS `example`.`test` $$
CREATE FUNCTION `example`.`test` (param INT) RETURNS VARCHAR(32)
BEGIN
DECLARE new_username VARCHAR(32);
SELECT `username`
INTO new_username
FROM `users`
WHERE `ID` = param;
RETURN COALESCE(new_username, 'Username not found');
END $$
Mind that the VARCHAR length of the RETURN value matches the variable, which should match the column length you want to return.