Getting errors when trying to create a PROCEDURE in mysql

Question

I am trying to create a mysql stored procedure, but I get this error:

Script line: 2 Failed to CREATE PROCEDURE proc_test_bideep

The stored procedure code 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 ;

mr_z_ro · Accepted Answer · 2014-01-20 15:26:18Z

1

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 (not BEGIN though), 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 ;

edited Jan 20, 2014 at 15:26

mr_z_ro

7838 silver badges16 bronze badges

answered Feb 11, 2010 at 22:38

Vince Bowdren

9,2853 gold badges37 silver badges61 bronze badges

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

Comments

tracy.brown · Accepted Answer · 2009-12-01 16:30:32Z

0

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).

answered Dec 1, 2009 at 16:30

tracy.brown

5894 silver badges9 bronze badges

Collectives™ on Stack Overflow

Getting errors when trying to create a PROCEDURE in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related