MySQL query returns unexpected results inside a stored procedure

MySQLstored-procedures

This is my stored procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `isUserValid`(IN `facebookId` VARCHAR(20), IN `userAccessToken` TEXT)
    NO SQL
    COMMENT 'check if user is valid. return true or false'
Begin
    /* declare the select resluts variables*/   
    Declare accessToken TEXT;
    Declare expires datetime;

    /* fill the variables with saved user's access token an expires time*/
    Select Access_Token, Expires
    Into accessToken, expires
    From Users Where Facebook_Id = facebookId;      

    select expires,accessToken;
    /* If the saved access token and expires are valid return true */
    If (BINARY accessToken = userAccessToken And expires > now()) Then
        select true As isUserValid;
    Else
        select false As isUserValid;
    End If; 
End

For some reason the query:

/* fill the variables with saved user's access token an expires time*/
Select Access_Token, Expires
Into accessToken, expires
From Users Where Facebook_Id = facebookId;

returns the Expires as null all the time.

When I'm running the same query outside of the procedure, it returns a valid value for Expires.

Best Answer

more correct change variable names:

CREATE DEFINER=`root`@`localhost` PROCEDURE `isUserValid`(IN `p_facebookId` VARCHAR(20), IN `p_userAccessToken` TEXT)
    NO SQL
    COMMENT 'check if user is valid. return true or false'
Begin
    /* declare the select resluts variables*/   
    Declare v_accessToken TEXT;
    Declare v_expires datetime;

    /* fill the variables with saved user's access token an expires time*/
    Select U.Access_Token, U.Expires
    Into v_accessToken, v_expires
    From Users U Where U.Facebook_Id = p_facebookId;      

    select v_expires, v_accessToken;
    /* If the saved access token and expires are valid return true */
    If (BINARY v_accessToken = p_userAccessToken And v_expires > now()) Then
        select true As isUserValid;
    Else
        select false As isUserValid;
    End If; 
End

it help read code more easy for You and for MySQL parser as well

Related Question