-1

I have the following stored procedure in mysql.

CREATE DEFINER=`cefcy`@`localhost` PROCEDURE `insert_eD_medication` (IN `p_id` INT(11), IN `strength` VARCHAR(45), IN `unit_id` INT(11), IN `package_no` VARCHAR(45), IN `medicinal_product_package` VARCHAR(45), IN `pharmaceutical_dose_form_id` INT(11), IN `brandName` VARCHAR(45), IN `route_of_administration_id` INT(11), IN `date_of_dispense` DATE, IN `substitution` VARCHAR(45), IN `stat` TINYINT(4), IN `v_id` INT(11))
    NO SQL
BEGIN
INSERT INTO `medication_dispensation_mapping` (`patient_id`, `strength`, `unit_id`, `package_no`, `medicinal_product_package`, `pharmaceutical_dose_form_id`,`brandName`, `route_of_administration_id`, `date_of_dispense_medicine_event`, `substitution`, `status`, `version_id`) 
VALUES (p_id, strength, unit_id, package_no, medicinal_product_package, pharmaceutical_dose_form_id, brandName, route_of_administration_id, date_of_dispense, substitution, stat, v_id);
END

When I'm trying to run it, I get the following error:

#1064 - 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

Can you help me please?

zinon
  • 4,427
  • 14
  • 70
  • 112

1 Answers1

1

Thanks to @DanFromGermany comment and the proposed How can I fix MySQL error #1064?, I found the solution.

Even thought I haven't changed the delimiter I had to define it in the stored procedure.

Thus, my correct code is:

DELIMITER $$
CREATE DEFINER=`cefcy`@`localhost` PROCEDURE `insert_eD_medication` (IN `p_id` INT(11), IN `strength` VARCHAR(45), IN `unit_id` INT(11), IN `package_no` VARCHAR(45), IN `medicinal_product_package` VARCHAR(45), IN `pharmaceutical_dose_form_id` INT(11), IN `brandName` VARCHAR(45), IN `route_of_administration_id` INT(11), IN `date_of_dispense` DATE, IN `substitution` VARCHAR(45), IN `stat` TINYINT(4), IN `v_id` INT(11))
    NO SQL
BEGIN
INSERT INTO `medication_dispensation_mapping` (`patient_id`, `strength`, `unit_id`, `package_no`, `medicinal_product_package`, `pharmaceutical_dose_form_id`,`brandName`, `route_of_administration_id`, `date_of_dispense_medicinal_event`, `substitution`, `status`, `version_id`) 
VALUES (p_id, strength, unit_id, package_no, medicinal_product_package, pharmaceutical_dose_form_id, brandName, route_of_administration_id, date_of_dispense, substitution, stat, v_id);
END;
$$
DELIMITER ;
zinon
  • 4,427
  • 14
  • 70
  • 112
  • I wasn't sure if you need a delimiter if you only have a single statement in the body but obv. one needs it.. – Daniel W. Jan 14 '19 at 12:14
  • You should review https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html – P.Salmon Jan 14 '19 at 12:15
  • 1
    @DanFromGermany You don't and in this case the begin..end counts as a statement - making 2 statements in the procedure. The begin..end are not necessary in this case since there is only an insert statement. – P.Salmon Jan 14 '19 at 12:17