SQL procedure parameter

Question

I am trying to do a procedure in sql but I have some trouble with date parameters

DELIMITER #
CREATE PROCEDURE conso(IN p_upcNameId VARCHAR(20), IN p_dateFrom DATE, IN p_dateTo DATE)     
BEGIN
    DECLARE done int default false;

    DECLARE v_cumule FLOAT;

    DECLARE v_reserve VARCHAR(40);
    DECLARE v_kg VARCHAR(40);
    DECLARE v_date DATETIME;

    DECLARE v_reserve_1 VARCHAR(40);
    DECLARE v_kg_1 VARCHAR(40);
    DECLARE v_date_1 DATETIME;

    DECLARE cur1 CURSOR FOR
        SELECT reserve, kg, date
        FROM
        (
            ( SELECT event_param_3 as reserve, event_param_4 as kg, event_datetime as date FROM events WHERE upcNameId = p_upcNameId AND event_code = 50 ORDER BY event_datetime DESC )
            UNION
            ( SELECT event_param_2 as reserve, event_param_3 as kg, event_datetime as date FROM events WHERE upcNameId = p_upcNameId AND event_code = 54 ORDER BY event_datetime DESC )

            UNION
            ( SELECT event_param_2 as reserve, event_param_3 as kg, event_datetime as date FROM events WHERE upcNameId = p_upcNameId AND event_code = 52 ORDER BY event_datetime DESC )
            UNION
            ( SELECT event_param_1 as reserve, event_param_2 as kg, event_datetime as date FROM events WHERE upcNameId = p_upcNameId AND event_code = 56 ORDER BY event_datetime DESC )

            UNION
            ( SELECT event_param_2 as reserve, event_param_3 as kg, event_datetime as date FROM events WHERE upcNameId = p_upcNameId AND event_code = 53 ORDER BY event_datetime DESC )
            UNION
            ( SELECT event_param_1 as reserve, event_param_2 as kg, event_datetime as date FROM events WHERE upcNameId = p_upcNameId AND event_code = 57 ORDER BY event_datetime DESC )
        ) as diffusion_programs
        ORDER BY date DESC;
    DECLARE cur2 CURSOR FOR
        SELECT reserve, kg, date
        FROM
        (
            ( SELECT event_param_3 as reserve, event_param_4 as kg, event_datetime as date FROM events WHERE upcNameId = p_upcNameId AND event_code = 50 ORDER BY event_datetime DESC )
            UNION
            ( SELECT event_param_2 as reserve, event_param_3 as kg, event_datetime as date FROM events WHERE upcNameId = p_upcNameId AND event_code = 54 ORDER BY event_datetime DESC )

            UNION
            ( SELECT event_param_2 as reserve, event_param_3 as kg, event_datetime as date FROM events WHERE upcNameId = p_upcNameId AND event_code = 52 ORDER BY event_datetime DESC )
            UNION
            ( SELECT event_param_1 as reserve, event_param_2 as kg, event_datetime as date FROM events WHERE upcNameId = p_upcNameId AND event_code = 56 ORDER BY event_datetime DESC )

            UNION
            ( SELECT event_param_2 as reserve, event_param_3 as kg, event_datetime as date FROM events WHERE upcNameId = p_upcNameId AND event_code = 53 ORDER BY event_datetime DESC )
            UNION
            ( SELECT event_param_1 as reserve, event_param_2 as kg, event_datetime as date FROM events WHERE upcNameId = p_upcNameId AND event_code = 57 ORDER BY event_datetime DESC )
        ) as diffusion_programs
        WHERE date >= p_dateFrom;
        AND date <= p_dateTo;
        ORDER BY date DESC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN cur1;
    OPEN cur2;

    /* Create temporary table */
    CREATE TEMPORARY TABLE tmp
    (
        date DATE,
        kg FLOAT
    );
    SET v_cumule = 0;
    FETCH cur2 INTO v_reserve_1, v_kg_1, v_date_1;

    forLoop: LOOP
        /* Actual row */
        FETCH cur1 INTO v_reserve, v_kg, v_date;

        /* Next row */
        FETCH cur2 INTO v_reserve_1, v_kg_1, v_date_1;

        /* Reserves differents TODO */
        /*IF v_reserve != v_reserve_1
        END IF;*/

        IF DATE(v_date) = DATE(v_date_1) THEN SET v_cumule = v_cumule + (v_kg_1 - v_kg);
        ELSE
            INSERT INTO tmp VALUES (DATE(v_date), v_cumule);
            SET v_cumule = 0;
        END IF;

        IF done THEN LEAVE forLoop;
        END IF;
    END LOOP;

    CLOSE cur1;
    CLOSE cur2;
END#

You see p_datefrom and p_dateto are some parameter that I want to pass when I call the function with :

DELIMITER ;
CALL conso('Name','2017-01-01','2018-01-01');
SELECT * FROM tmp;

As you can see this is an interval of dates. But I get this error :

1064 - Erreur de syntaxe près de 'AND date <= p_dateTo; ORDER BY date DESC; DECLARE CONTINUE HANDLER' à la ligne 53

Can you help me please ?

Answer 1

There's some spurious semicolons (statement terminators).

  WHERE date >= p_dateFrom;
       AND date <= p_dateTo;
       ORDER BY date DESC;

Remove those

  WHERE date >= p_dateFrom
       AND date <= p_dateTo
       ORDER BY date DESC
  ;

---

original (wrong) answer:

The CONTINUE HANDLER has to follow the variable declarations, but needs to be before the cursor declarations. (I think this is vaguely documented in the MySQL Reference Manual. MySQL stored programs are very particular about the order of statements.)

To fix that error, just move the DECLARE CONTINUE HANDLER statement to right before the first DECLARE CURSOR.