Mysql syntax help

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.开发者_JAVA百科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;

---

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 ;

---

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