[SOLVED] Stored procedure Mysql BEGIN END

Stored procedure Mysql BEGIN END

I am writing a simple stored procedure to take data and enter it into a table

CREATE DEFINER = 'gunsdb-3939c99d-COPY-39393fb6'@'%'
PROCEDURE `gunsdb-3939c99d-COPY-39393fb6`.sp_entity_save(IN p_entity_id INT, IN p_entity_type_id INT, IN p_title_id INT, IN p_first_name VARCHAR(255), IN p_last_name VARCHAR(255), IN p_street_line_1 VARCHAR(255), IN p_street_line_2 VARCHAR(255), IN p_street_line_3 VARCHAR(255), IN p_town VARCHAR(255), IN p_county VARCHAR(255), IN p_postcode VARCHAR(255), IN p_email VARCHAR(255), IN p_telephone INT, IN p_company_name VARCHAR(500), IN p_company_desc VARCHAR(500), IN p_website VARCHAR(500), IN p_rfd_number INT, IN p_issuing_force VARCHAR(255), IN p_concent VARCHAR(255))
BEGIN    
INSERT INTO tbl_entity (
entity_id,
entity_type_id,
title_id,
first_name,
last_name,
street_line_1,
street_line_2,
street_line_3,
town,county,
postcode,
email,
telephone,
company_name,
company_desc,
website,
rfd_number,
issuing_force,
concent
)

VALUES
(
p_entity_id,
p_entity_type_id,
p_title_id,
p_first_name,
p_last_name,
p_street_line_1 , 
p_street_line_2,
p_street_line_3 ,
p_town, @county , 
p_postcode,
p_email ,
p_telephone,
p_company_name , 
p_company_desc, 
p_website, 
p_rfd_number , 
p_issuing_force , 
p_concent
)
ON DUPLICATE KEY UPDATE
    entity_type_id = p_entity_type_id, 
    title_id = p_title_id, 
    first_name = p_first_name, 
    last_name = p_last_name, 
    street_line_1 = p_street_line_1,
    street_line_2 = p_street_line_2,
    street_line_2 = p_street_line_2,
    town = p_town,
    county= p_county,
    postcode = p_postcode,
    email = p_email,
    telephone = p_telephone,
    company_name = p_company_name,
    company_desc = p_company_desc,
    website = p_website,
    rfd_number = p_rfd_number,
    issuing_force = p_issuing_force,
    concent = p_concent  
END

The above is throwing an error but as soon as a remove the BEGIN and END it is working fine and I don't understand why.

I have tried adding in DELIMITER but just seem to get the same issue:

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 'END' at line 77 SQL1.sql 77 3

I am using dbforge but I have also tried in Navicat and getting the same issue.

Solution

Did you use proper delimiter? In your code there's no delimiter at the end of insert.
You should use delimiter at the end of insert.

delimiter //
CREATE 
PROCEDURE sp_entity_save(IN p_entity_id INT, IN p_concent VARCHAR(255))
BEGIN    
INSERT INTO tbl_entity (
entity_id,
concent
)
VALUES
(
p_entity_id,
p_concent
)
ON DUPLICATE KEY UPDATE
    -- delimiter here
    concent = p_concent;
-- delimiter here
END //