Mysql syntax error creating stored procedure

Question

This is driving me nuts.

 CREATE DEFINER=`root`@`localhost` PROCEDURE `CalcularCRTarea` (Id_Tarea INT, OUT crTarea decimal(12, 4))
    DETERMINISTIC
BEGIN
  DECLARE done BOOLEAN DEFAULT FALSE;
  DECLARE _id BIGINT UNSIGNED;
  DECLARE cur CURSOR FOR SELECT Id FROM Tarea_Frente where Id_Item_Tarea = Id_Tarea;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;
  OPEN cur;
  testLoop: LOOP
    FETCH cur INTO _id;
    IF done THEN
      LEAVE testLoop;
    ELSE 
        CALL CalcularCRFrente(_id, @suma);
        SET crTarea = crTarea + @suma;
    END IF;
  END LOOP testLoop;

  CLOSE cur;      
END;

phpmyadmin returns me

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 4

that would be the line

  DECLARE done BOOLEAN DEFAULT FALSE;

what's wrong here ? I tried with several minor changes and I'm getting always the same error. I checked the documentation and I'm not seeing what's the syntax error. I know this is a silly error but I'm stucked with this like a half an hour. Is a cursor that iterates each row and executes a stored procedure for each one; I've done it with stored functions (and it worked like a charm) but at the time to publish in the server I saw that my shared hosting doesn't suppor stored functions, only stored procedures :( So it's the best way I've found.

I tested on MySQL 5.6.16 and it works fine with no error. Are you sure you posted the right code? Any hidden weird whitespace characters that got filtered out when you posted here? — Bill Karwin
– Bill Karwin, Commented Apr 18, 2014 at 18:36
It is the right code. I thought on hidden weird whitespace, but I deleted everyone after each ";". I'm using MySQL 5.5.35 — Gonzalo.-
– Gonzalo.-, Commented Apr 18, 2014 at 18:38

Ravinder Reddy · Accepted Answer · 2014-04-18 18:40:46Z

6

You are missing custom delimiter instruction.

As you missed it, engine tried to compile the statements when it found the first default statement terminator, the semicolon ;. And hence the exception.

Place your entire routine in between:

-- define the delimiter
delimiter //


--- place here your stored procedure

-- and lastly following line
//

-- reset the delimiter
delimiter ;

answered Apr 18, 2014 at 18:40

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

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

5 Comments

Gonzalo.- Over a year ago

great, it worked. I thought it was not neccesary, as in some cases I could create stored without using this. Why is neccesary?

Grijesh Chauhan Over a year ago

I guess this must be the correct reason, Did you guess or try?

Ravinder Reddy Over a year ago

@Gonzalo.-: Usually stored procedures, including triggers comprise a set of executable statements. But they all are part of a single body but not individual statements. To group them we need to instruct the engine with a new statement or block terminator. It is done using delimiter.

Ravinder Reddy Over a year ago

@Gonzalo.-: And usually most GUI based SQL interpreters allow you not to use them. You can also see on SQL Fiddle that it has a drop down list to chose custom delimiter to use on your statemenets.

Gonzalo.- Over a year ago

Yes, phpmyadmin has a delimiter option on the above, I just saw it

Collectives™ on Stack Overflow

Mysql syntax error creating stored procedure

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related