error when creating PROCEDURE on mysql

Question

Am trying to create a mysql stock procedure, but i have the following error

Script line: 2 Failed to CREATE PROCEDURE proc_test_bideep

The syntax proc is:

DELIMITER $$

DROP PROCEDURE IF EXISTS `commun`.`insert_categorie` $$
CREATE PROCEDURE `commun`.`insert_categorie` (id_mere INT,
                                                                lib_categ VARCHAR(50),
                                                                id_categ_sup INT ,
        categ_authInstantBuy INT)
BEGIN
SET @bg_mere := (SELECT categ_bg FROM categ_basic WHERE categ_id = id_mere);

 @bg_mere+2,categ_level_bideep,categ_statut,categ_adult,categ_authSmallBid,categ_authBid,categ_authInstantBuy);
        SELECT '1' AS code_retour;   END IF;
ecetera.........
END $$

DELIMITER ;

Answer 1

Your parameters are missing the keyword IN such as: ...(IN id_mere INT, IN lib_categ ...). Also, you need to configure your OUT variable for @bg_mere in the initial parameter list such as (IN xxx, ..., OUT bg_mere VARCHAR/INT/WHATEVER).

Answer 2

a) You need to DECLARE any variables on the first lines of the procedure, including their datatype:

DECLARE bg_mere INT;

b) To fetch a value from the database into a variable, you use SELECT ... INTO syntax:

SELECT categ_bg INTO bg_mere FROM categ_basic WHERE categ_basic.categ_id = id_mere;

c) You have an END IF without the corresponding IF.

d) The closing END needs a semicolon, only then do you need a delimiter to finish the entire statement, and finally you should reset the delimiter back to normal:

BEGIN;
// body of the stored procedure goes here
END;
$$
DELIMITER ;