mysql stored function parameter

Question

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

Paiz

Answer 1

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.

Dogear

Thanks I tried that but get the error: Can't create a FUNCTION from within another stored routine – Paiz

@Elliot: Execute the code in a separate script tab--somehow you've managed to paste it inside of an existing function/stored procedure declaration. – Dogear

@Elliot: What do you want to do in the event the username is NULL? You realize that the return value will also be NULL if someone runs the function with a parameter value that doesn't exist in the table? – Dogear

Yes, I am checking if it doesn't exist then new_username will equal "Username not found" . Thanks again. – Paiz