0

I'm getting error #1064 "syntax error". I try to change the name of the variable DECLARE CheckExists int;

and compared it with mysql 8 syntax but still not working!! There is my stored procedure code below. and a scan of error message There is 

CREATE DEFINER=`root`@`localhost` PROCEDURE `INSERT_JP_USERS`(
    IN `Email_Param` VARCHAR(50),
    IN `First_Name_Param` VARCHAR(50),
    IN `Middle_Name_Param` VARCHAR(50),
    IN `Last_Name_Param` VARCHAR(50),
    IN `Gender_Param` VARCHAR(50),
    IN `Phone_Number_Param` VARCHAR(50),
    IN `Summary_Param` VARCHAR(50),
    IN `Experience_Param` INT,
    IN `Profile_Img_Param` VARCHAR(50),
    OUT `Result_Param` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

DECLARE CheckExists int;

SELECT count(*) INTO CheckExists FROM jp_users WHERE email = Email_Param;

IF (CheckExists > 0) THEN

UPDATE jp_users SET first_name = First_Name_Param, middle_name = Middle_Name_Param, last_name = Last_Name_Param,
gender = Gender_Param, phone_number = Phone_Number_Param, summary = Summary_Param, experience = Experience_Param, profile_img = Profile_Img_Param WHERE email = Email_Param;

SET Result_Param = 0;

SELECT Result_Param;

ELSE 

INSERT INTO jp_users (email, first_name, middle_name, last_name, gender, phone_number, summary, experience, profile_img)
 
VALUES (Email_Param, First_Name_Param, Middle_Name_Param, Last_Name_Param, Gender_Param, Phone_Number_Param, Summary_Param, Experience_Param, Profile_Img_Param);

SELECT COUNT(*) INTO Result_Param FROM jp_users WHERE email = Email_Param;

SELECT Result_Param;
 
END IF;

END
Shadow
  • 33,525
  • 10
  • 51
  • 64
selkarimi
  • 1
  • I tested this CREATE PROCEDURE statement on a MySQL 5.7 instance, but I got no error. Can you please edit your question and post the full error message? – Bill Karwin Sep 30 '21 at 14:45
  • @BillKarwin syntax error near '' in a stored proc after begin is a sign for missing delimiter specification. If you test the code in a client that dors not require delimiter, then the code will work. – Shadow Sep 30 '21 at 14:47
  • the first semicolon encountered after the CREATE PROCEDURE is terminating the statement. in several mysql clients (like mysql command line client) it is necessary to change the statement terminator character(s) to a string that does not appear in the procedure definition, then execute the CREATE PROCEDURE and then set the statement terminator back to the default semicolon. ie. `DELIMITER $$` then `CREATE PROCEDURE .... END$$` and then `DELIMITER ;` – spencer7593 Sep 30 '21 at 16:01
  • The Error Like you describe above was related to the missing delimiter specification, so when I follow the resolution that @spencer7593 describe, it work good. Thank you – selkarimi Sep 30 '21 at 21:18

0 Answers0