MySQL Error: 1337

Question

I'm getting this error while trying to create a procedure in MySQL:

Error Code: 1337
Variable or condition declaration after cursor or handler declaration

even after doing a lot of Googling I found no relevant solution to my problem. My procedure is as below:

DELIMITER //
CREATE PROCEDURE emp_dates(IN startDate DATE, IN endDate DATE)
BEGIN
DECLARE fromDt DATETIME;
DECLARE toDt DATETIME;
DECLARE done INT DEFAULT FALSE;
DECLARE employees CURSOR FOR SELECT empid FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
DECLARE emp VARCHAR(20);
SET fromDt=startDate;
SET toDt=endDate;

OPEN employees;

WHILE fromDt<=toDt DO
REPEAT
    FETCH employees INTO emp;
    IF NOT done THEN
    INSERT INTO new_attendance_2(attid,empid,dt) VALUES(DEFAULT,emp,fromDt);
    END IF
UNTIL done END REPEAT;
SET fromDt=DATE_ADD(fromDt, INTERVAL 1 DAY);
LOOP
CLOSE employees;
END
DELIMITER ;

The purpose is to create a procedure which will take two dates as input and insert records in other table for every day that comes in between of those two. Somebody please help! I appreciate any little help. Thanks a lot in advance.

Answer 1

It's complaining about:

Variable or condition declaration after cursor or handler declaration

So I would be looking at that location, where you do indeed declare variables after the cursor and handler:

   DECLARE employees CURSOR FOR SELECT empid FROM employees;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
>> DECLARE emp VARCHAR(20);

If you modify your declarations thus, it should be okay:

DECLARE fromDt DATETIME;
DECLARE toDt DATETIME;
DECLARE done INT DEFAULT FALSE;
DECLARE emp VARCHAR(20);

DECLARE employees CURSOR FOR SELECT empid FROM employees;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;

The order has to be, as per here:

Cursor declarations must appear before handler declarations and after variable and condition declarations.