CODEWITHSUNDEEP
mysqlstored-procedures
H Dindi
H Dindi

Reputation: 1552

MySQL said: Documentation #1064 - You have an error in your SQL syntax; on Stored Procedure

I am trying to create teh following procedure : 

CREATE  PROCEDURE `client_clinical_visit`()
    DETERMINISTIC
    COMMENT 'Client clinical visit '
BEGIN
    SELECT 
        `db565263480`.`kp_bio_data`.`uuid` AS `uuid`,
        `db565263480`.`kp_bio_data`.`firstname` AS `firstname`,
        `db565263480`.`kp_bio_data`.`lastname` AS `lastname`,
        `db565263480`.`kp_bio_data`.`id` AS `kp_bio_data_id`,
        `db565263480`.`kp_bio_data`.`tel` AS `tel`,
        `db565263480`.`kp_bio_data`.`gender` AS `gender`,
        `db565263480`.`kp_bio_data`.`marital_status` AS `marital_status`,
        `db565263480`.`kp_bio_data`.`education_level` AS `education_level`,
        `db565263480`.`kp_bio_data`.`place_of_birth` AS `place_of_birth`,
        `db565263480`.`kp_types`.`id` AS `kp_types_id`,
        `db565263480`.`kp_types`.`Name` AS `name`,
        `db565263480`.`kp_types`.`Abbrv` AS `abbrv`,
        `db565263480`.`clinic_visit`.`disc_no` AS `disc_no`
    FROM
        ((`kp_bio_data`
        INNER JOIN `kp_types` ON ((`db565263480`.`kp_bio_data`.`kp_id` = `db565263480`.`kp_types`.`id`)))
       INNER JOIN `clinic_visit` ON ((`db565263480`.`clinic_visit`.`uuid` = `db565263480`.`kp_bio_data`.`uuid`)));
END

but I keep getting the following error : 

MySQL said: Documentation

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

Please advise on how to solve this...

Upvotes: 0

Views: 96

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

You perhaps missed the delimiter and in the select query the braces are not needed so just removed them, 

delimiter //

CREATE  PROCEDURE `client_clinical_visit`()
    DETERMINISTIC
    COMMENT 'Client clinical visit '
BEGIN
    SELECT 
        `db565263480`.`kp_bio_data`.`uuid` AS `uuid`,
        `db565263480`.`kp_bio_data`.`firstname` AS `firstname`,
        `db565263480`.`kp_bio_data`.`lastname` AS `lastname`,
        `db565263480`.`kp_bio_data`.`id` AS `kp_bio_data_id`,
        `db565263480`.`kp_bio_data`.`tel` AS `tel`,
        `db565263480`.`kp_bio_data`.`gender` AS `gender`,
        `db565263480`.`kp_bio_data`.`marital_status` AS `marital_status`,
        `db565263480`.`kp_bio_data`.`education_level` AS `education_level`,
        `db565263480`.`kp_bio_data`.`place_of_birth` AS `place_of_birth`,
        `db565263480`.`kp_types`.`id` AS `kp_types_id`,
        `db565263480`.`kp_types`.`Name` AS `name`,
        `db565263480`.`kp_types`.`Abbrv` AS `abbrv`,
        `db565263480`.`clinic_visit`.`disc_no` AS `disc_no`
    FROM
        `kp_bio_data`
        INNER JOIN `kp_types` ON `db565263480`.`kp_bio_data`.`kp_id` = `db565263480`.`kp_types`.`id`
       INNER JOIN `clinic_visit` ON `db565263480`.`clinic_visit`.`uuid` = `db565263480`.`kp_bio_data`.`uuid`;
END;//

delimiter ;

Here is a test case in mysql

mysql> delimiter //
mysql> 
mysql> CREATE  PROCEDURE `client_clinical_visit`()
    ->     DETERMINISTIC
    ->     COMMENT 'Client clinical visit '
    -> BEGIN
    ->     SELECT 
    ->         `db565263480`.`kp_bio_data`.`uuid` AS `uuid`,
    ->         `db565263480`.`kp_bio_data`.`firstname` AS `firstname`,
    ->         `db565263480`.`kp_bio_data`.`lastname` AS `lastname`,
    ->         `db565263480`.`kp_bio_data`.`id` AS `kp_bio_data_id`,
    ->         `db565263480`.`kp_bio_data`.`tel` AS `tel`,
    ->         `db565263480`.`kp_bio_data`.`gender` AS `gender`,
    ->         `db565263480`.`kp_bio_data`.`marital_status` AS `marital_status`,
    ->         `db565263480`.`kp_bio_data`.`education_level` AS `education_level`,
    ->         `db565263480`.`kp_bio_data`.`place_of_birth` AS `place_of_birth`,
    ->         `db565263480`.`kp_types`.`id` AS `kp_types_id`,
    ->         `db565263480`.`kp_types`.`Name` AS `name`,
    ->         `db565263480`.`kp_types`.`Abbrv` AS `abbrv`,
    ->         `db565263480`.`clinic_visit`.`disc_no` AS `disc_no`
    ->     FROM
    ->         `kp_bio_data`
    ->         INNER JOIN `kp_types` ON `db565263480`.`kp_bio_data`.`kp_id` = `db565263480`.`kp_types`.`id`
    ->        INNER JOIN `clinic_visit` ON `db565263480`.`clinic_visit`.`uuid` = `db565263480`.`kp_bio_data`.`uuid`;
    -> END;//
Query OK, 0 rows affected (0.08 sec)

Upvotes: 1

Related Questions