MySQL: Stored Procedure Syntax Error

Question

I am writing a stored procedure in phpmyadmin but I am getting a syntax error near Update statement. But if I put my update statement inside my if statement than all errors been resolved.

Q: Why update statement outside causes an error?

CREATE DEFINER=`root`@`localhost` PROCEDURE `tbl_people_update`(IN `p_Id` INT, IN `p_Name` VARCHAR(150), IN `p_Mobile_Number` VARCHAR(100))
    IF CHAR_LENGTH(p_Mobile_Number) <= 0 THEN
     SET p_Mobile_Number = '12345';
    END IF;

    UPDATE tbl_people
     SET 
     Name=p_Name,
     Mobile_Number = p_Mobile_Number
     WHERE
     Id=p_Id;

The error is as follows:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE tbl_people SET Name=p_Name, Mobile_Number = p_Mobile_Number WHER' at line 5

Sagar Gangwal · Accepted Answer · 2017-04-20 10:00:36Z

1

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `tbl_people_update`(IN `p_Id` INT, IN `p_Name` VARCHAR(150), IN `p_Mobile_Number` VARCHAR(100))
BEGIN
     IF CHAR_LENGTH(p_Mobile_Number) <= 0 THEN
     SET p_Mobile_Number = '12345';
    END IF;
    UPDATE tbl_people
     SET 
     Name=p_Name,
     Mobile_Number = p_Mobile_Number
     WHERE
     Id=p_Id;
END$$
DELIMITER ;

Try above code.

Hope this will helps you.

answered Apr 20, 2017 at 10:00

Sagar Gangwal

8,1303 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Blank · Accepted Answer · 2017-04-20 09:54:59Z

1

You've forgotten re-define delimiter, try this:

delimiter ||
CREATE DEFINER=`root`@`localhost` PROCEDURE `tbl_people_update`(IN `p_Id` INT, IN `p_Name` VARCHAR(150), IN `p_Mobile_Number` VARCHAR(100))
    IF CHAR_LENGTH(p_Mobile_Number) <= 0 THEN
     SET p_Mobile_Number = '12345';
    END IF;

    UPDATE tbl_people
     SET 
     Name=p_Name,
     Mobile_Number = p_Mobile_Number
     WHERE
     Id=p_Id;
||
delimiter ;

answered Apr 20, 2017 at 9:54

Blank

12.4k1 gold badge16 silver badges34 bronze badges

Collectives™ on Stack Overflow

MySQL: Stored Procedure Syntax Error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related