Mysql syntax error on stored procedure

Question

I run this code as a sql script from command line, and I get "you have an error in your SQL syntax" almost in all lines! Any ideas what is wrong here?

CREATE PROCEDURE updatemandate()
 BEGIN
   DECLARE _mandate_id BIGINT(20);
   DECLARE _has_succesful_payment tinyint(1);
   DECLARE done INT DEFAULT 0;
   DECLARE cnt INT;
   DECLARE mandateCursor CURSOR FOR Select mandate_id, has_succesful_payment From mandates;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 
   OPEN mandateCursor;
   allmandates: LOOP
   Fetch mandateCursor INTO _mandate_id, _has_succesful_payment;
   IF done THEN LEAVE allmandates;
   END IF;
    Select COUNT(*) FROM payments WHERE mandate_id=_mandate_id AND status='OK' into cnt; 
            IF cnt>0 THEN
            SET _has_succesful_payment=1;
            END IF;

   END LOOP allmandates;
   CLOSE mandateCursor;
 END

Eric Petroelje · Accepted Answer · 2012-12-17 15:58:12Z

3

The ; character is the default delimiter, so when MySQL sees the first ; it thinks you are done. When you create a sproc, you need to declare a different delimiter character, like so:

DELIMITER $$

CREATE PROCEDURE updatemandate()
READS SQL DATA
 BEGIN
   DECLARE _mandate_id BIGINT(20);
   DECLARE _has_succesful_payment tinyint(1);
   DECLARE done INT DEFAULT 0;
   DECLARE cnt INT;
   DECLARE mandateCursor CURSOR FOR Select mandate_id, has_succesful_payment From mandates;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 
   OPEN mandateCursor;
   allmandates: LOOP
   Fetch mandateCursor INTO _mandate_id, _has_succesful_payment;
   IF done THEN LEAVE allmandates;
   END IF;
    Select COUNT(*) FROM payment WHERE mandate_id=_mandate_id AND status='OK' into cnt; 
            IF cnt>0 THEN
            SET _has_succesful_payment=1;
            END IF;

   END LOOP allmandates;
   CLOSE mandateCursor;
 END$$

DELIMITER ;

Also a good idea to add the READS SQL DATA to the sproc definition in case you need to support binary logging.

edited Dec 17, 2012 at 15:58

answered Dec 17, 2012 at 15:52

Eric Petroelje

60.7k9 gold badges133 silver badges178 bronze badges

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

9 Comments

Spring Over a year ago

thank you worked, but another interesting thing is; it works from command line as a sql script but give syntax errors when I copy paste the code to a Sql commander and try to execute from there, any idea?

Eric Petroelje Over a year ago

@Spring - not sure, I've never use SQL commander - It does work fine from MySQL Workbench.

Cynical Over a year ago

Which error is produced? Which "Sql commander" are you using the command with?

Eric Petroelje Over a year ago

Did a quick google search - looks like SQL Commander has it's own special syntax for using the delimiter command: dbvis.com/doc/7.0/doc/ug/sqlCommander/…

Eric Petroelje Over a year ago

@Spring - looks like all you are doing is updating the _has_succesful_payment variable. You would need to issue an actual UPDATE statement in your loop to change the value in the table.

|

Cynical · Accepted Answer · 2012-12-17 15:54:17Z

0

Have you tried putting delimiter // before the CREATE statement and change the last line with END //?

answered Dec 17, 2012 at 15:54

Cynical

9,6281 gold badge19 silver badges34 bronze badges

Collectives™ on Stack Overflow

Mysql syntax error on stored procedure

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related