MySQL query returns unexpected results inside a stored procedure

Question

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.

• ypercubeᵀᴹ: Try using different names for the variables, not the same as the columns (accessToken is different than Access_Token but expires is the same as Expires)
• Asaf Nevo: yes that actually was the problem.. post it as answer and i will accept it

Answer 1

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

Answer 2

Actually you can alias query and solve the problem like this:

Select Users.Access_Token, Users.Expires Into accessToken, expires From Users Where Facebook_Id = facebookId;