CODEWITHSUNDEEP
mysql
MTA
MTA

Reputation: 1073

MySQL: Stored Procedure Syntax Error

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

Upvotes: 0

Views: 2560

Answers (2)

Sagar Gangwal
Sagar Gangwal

Reputation: 7937

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.

Upvotes: 1

Blank
Blank

Reputation: 12378

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 ;

Upvotes: 1

Related Questions