Mysql syntax help

Question

I have problems writing a SP in MySQL. From what I can read on the net the following should work, but it does not. I use Mysql v. 5.1.35

CREATE DEFINER=`My_Username`@`%` PROCEDURE `ChangePassword`(IN SPusername VARCHAR(100),
    IN SPoldPassword VARCHAR(100),
    IN SPnewPassword VARCHAR(100))
BEGIN
IF EXISTS(SELECT Password_Hash 
       FROM Customer
       WHERE SPusername = Email 
       AND  SPoldPassword = Password_Hashed)
THEN
UPDATE Customer
SET Password_Hashed = SPnewPassword
END IF;
END;

Answer 1

It would seem that you might be missing the WHERE statement from the IF EXISTS check in the UPDATE statement.

Change it to

UPDATE Customer
SET Password_Hashed = SPnewPassword
WHERE SPusername = Email
AND  SPoldPassword = Password_Hashed

Answer 2

Hi Unknown

I think it's because the procedure name should not be in ''s.

In the first line, try this:

CREATE DEFINER='My_Username'@'%' PROCEDURE ChangePassword(IN SPusername VARCHAR(100),

Ohh ... and remember to also look at astander's answer regarding your WHERE clause in the UPDATE statement. Without extending the WHERE clause all passwords would be set to the same thing ... every time! He is advise is good! (Although the SQL statement would work) :-)

Regards
     Sigersted

PS: The complete query (with delimiter and astander's advice):

DELIMITER //
CREATE DEFINER='My_Username'@'%' PROCEDURE ChangePassword(
    IN SPusername VARCHAR(100),
    IN SPoldPassword VARCHAR(100),
    IN SPnewPassword VARCHAR(100))
BEGIN
IF EXISTS(SELECT Password_Hash 
          FROM Customer
          WHERE SPusername = Email 
          AND  SPoldPassword = Password_Hashed)
THEN
  UPDATE Customer
  SET Password_Hashed = SPnewPassword
  WHERE SPusername = Email 
  AND  SPoldPassword = Password_Hashed;
END IF;
END//
DELIMITER ;