-
MySQL Stored Procedure
I've got a database with a Users table that has the fields: EmailAddress VarChar(200) and PasswordHash VarChar(200), the PasswordHash is a SHA1() encrypted password.
What I'm trying to do is have the stored procedure accept an email address and the password, it will then return either a -1 (if the un/pass combination is bad) or the UserID value if the combination is good.
Here's what I have so far:
Code:
USE gr_jci;
DELIMITER $$
/* Create Login procedure */
DROP PROCEDURE IF EXISTS `spGetUserID`$$
CREATE PROCEDURE `spGetUserID`(IN _EmailAddress VarChar(200), IN _Password VarChar(50))
DETERMINISTIC
BEGIN
Select (Case When u.UserID Is Null Then -1 Else u.UserID End) As UserID
From Users u
Where u.EmailAddress = _EmailAddress
And u.PasswordHash = SHA1(_Password)
And Active = 1;
END$$
DELIMITER ;
MySQL accepts that as valid sql but when I call the stored procedure I get the error:
Code:
Static analysis:
1 errors were found during analysis.
Unrecognized keyword. (near "ON" at position 25)
MySQL said:
#2014 - Commands out of sync; you can't run this command now
I've also tried:
Code:
USE gr_jci;
DELIMITER $$
/* Create Login procedure */
DROP PROCEDURE IF EXISTS `spGetUserID`$$
CREATE PROCEDURE `spGetUserID`(IN _EmailAddress VarChar(200), IN _Password VarChar(50))
DETERMINISTIC
BEGIN
Select If(u.UserID Is Null, -1, u.UserID) As UserID
From Users u
Where u.EmailAddress = _EmailAddress
And u.PasswordHash = SHA1(_Password)
And Active = 1;
END$$
DELIMITER ;
As well as:
Code:
USE gr_jci;
DELIMITER $$
/* Create Login procedure */
DROP PROCEDURE IF EXISTS `spGetUserID`$$
CREATE PROCEDURE `spGetUserID`(IN _EmailAddress VarChar(200), IN _Password VarChar(50))
DETERMINISTIC
BEGIN
Select IFNULL(u.UserID, -1) As UserID
From Users u
Where u.EmailAddress = _EmailAddress
And u.PasswordHash = SHA1(_Password)
And Active = 1;
END$$
DELIMITER ;
But both return the same error message.
Anyone have any idea why or have any suggestions on how to return either a -1 or the UserID integer in the record?
