CODEWITHSUNDEEP
mysqlsqlstored-procedures
T Varcor
T Varcor

Reputation: 507

MySQL stored procedure not returning correct results

I have a stored procedure that checks if a user exists already, regardless of the input used for the email it comes back with the first line in the database. If I run the select statements manually I get the correct results. Any suggestions why?

CREATE DEFINER=`myusername`@`localhost` PROCEDURE `User_Auth`(IN `Email` VARCHAR(30))
BEGIN
    DECLARE User_ID INT(30);
        SELECT `User ID` INTO User_ID FROM `Users` WHERE `Email` = Email LIMIT 1;

    IF (User_ID > 0) THEN
        SELECT * FROM `Users` WHERE `User ID` = User_ID;
    ELSE
        SELECT concat('No Users Found: ', Customer_Name);
    END IF;
END

Upvotes: 2

Views: 772

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

The problem is that your variable has the name email, the same as the column. So, email = email is basically a no-op, because it refers only to the column name.

It is a good idea to prefix parameters with something to avoid this confusion:

CREATE DEFINER=`myusername`@`localhost` PROCEDURE `User_Auth`(IN v_Email VARCHAR(30))
BEGIN
    DECLARE v_User_ID INT(30);
    SELECT `User ID` INTO User_ID FROM `Users` u WHERE u.Email = v_Email LIMIT 1;

    IF (User_ID > 0) THEN
        SELECT * FROM `Users` WHERE `User ID` = v_User_ID;
    ELSE
        SELECT concat('No Users Found: ', Customer_Name);
    END IF;
END

Upvotes: 1

Andrew Barber
Andrew Barber

Reputation: 40149

Your problem no doubt lies here:

SELECT `User ID` INTO User_ID FROM `Users` WHERE `Email` = Email LIMIT 1;

To whit; your parameter name is the same as your column name. That can actually work, but the rules to make it do so can be weird. (I think if you switched the quotes around, it might work).

But really, what you should do is rename the Email parameter to something else:

CREATE DEFINER=`myusername`@`localhost` PROCEDURE `User_Auth`(IN `EmailAddress` VARCHAR(30))
BEGIN
    DECLARE User_ID INT(30);
        SELECT `User ID` INTO User_ID FROM `Users` WHERE `Email` = EmailAddress  LIMIT 1;

    IF (User_ID > 0) THEN
        SELECT * FROM `Users` WHERE `User ID` = User_ID;
    ELSE
        SELECT concat('No Users Found: ', Customer_Name);
    END IF;
END

Upvotes: 5

Related Questions