MySQL Syntax Error With Stored Procedures

Question

I must be missing something simple because I can't figure out what is causing my script to fail. Below is the stored procedure I've written:

CREATE PROCEDURE `Search_contacts`(IN `in_owner_id` INT,
    IN `in_first_name` VARCHAR(255))
 IF in_first_name IS NOT NULL THEN
     SELECT * FROM `contacts`
     WHERE `owner_id` = in_owner_id AND `first_name` LIKE in_first_name;
END IF;

When I try and execute this on my MySQL server I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5

I'd like to know what is causing this error and why so I can avoid it again.

Any help is appreciated!

paulsm4 · Accepted Answer · 2021-05-22 01:35:46Z

2

Try adding "BEGIN", "END" and "DELIIMITER", like this:

DELIMITER $$
CREATE PROCEDURE `Search_contacts`(IN `in_owner_id` INT,
    IN `in_first_name` VARCHAR(255))
BEGIN
    IF in_first_name IS NOT NULL THEN
        SELECT * FROM `contacts`
        WHERE `owner_id` = in_owner_id AND `first_name` LIKE in_first_name;
    END IF;
END $$
DELIMITER ;

edited May 22, 2021 at 1:35

answered May 22, 2021 at 1:30

paulsm4

123k23 gold badges175 silver badges245 bronze badges

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

1 Comment

rctfan1999 Over a year ago

I had a feeling I was forgetting something simple, thank you! Is it best practice to always include "DELIMITER", "BEGIN" and "END"?

Collectives™ on Stack Overflow

MySQL Syntax Error With Stored Procedures

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related