Create function Error in phpmyadmin

Question

when I try to create a function to retrieve userName from user table using their email it gives me this useless error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE name VARCHAR; BEGIN select userName AS name from user WHERE `ema' at line 2

, the same code with a different syntax works in mssql so I wonder what is the difference? in better words what am I doing wrong here?

DELIMITER ;;
CREATE FUNCTION getUserName(email varchar(50)) RETURNS VARCHAR
BEGIN
    DECLARE name VARCHAR;
    SELECT `userName` AS name FROM `user` WHERE `email` = email;
    RETURN name;
END ;;

dkasipovic · Accepted Answer · 2014-03-26 23:12:40Z

2

Well, first of all, varchar needs to have length, which you lack in two places. Also, you need to select into the variable and return the variable, because you cannot return resultset. Also you should escape name, and you do not need to alias your column because you are selecting into anyway. So, your code should be like this:

DELIMITER ;;
CREATE FUNCTION getUserName(email VARCHAR(50)) RETURNS VARCHAR(50)
BEGIN
    DECLARE NAME VARCHAR(50);
    SELECT `userName` FROM `user` WHERE `email` = email
    INTO `name`;
    RETURN `name`;
END ;;

answered Mar 26, 2014 at 23:12

dkasipovic

6,1301 gold badge23 silver badges27 bronze badges

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

1 Comment

user3466364 Over a year ago

it worked a treat thank you, and I like your comments now I know why it didn't work before

Collectives™ on Stack Overflow

Create function Error in phpmyadmin

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related