CODEWITHSUNDEEP
mysqlstored-procedures
Oleksandr H
Oleksandr H

Reputation: 3015

MySQL Stored Procedure Delimiter

I try to create following procedure:

DROP PROCEDURE IF EXISTS my_super_procedure;
DELIMITER $$
CREATE PROCEDURE my_super_procedure(IN in_id VARCHAR(255), IN in_from_date timestamp, IN in_to_date timestamp)
BEGIN

    DROP TEMPORARY TABLE IF EXISTS tblResults;
    CREATE TEMPORARY TABLE IF NOT EXISTS tblResults(
        selected_date timestamp,
        total_value int
    ); -- !!!!!  Syntax error: missing END

    DECLARE tmp_date timestamp;
    SET tmp_date = in_from_date;

    WHILE tmp_date < in_to_date DO

        SELECT count(*) as total_events FROM event e where e.scheduled between tmp_date and tmp_date + interval 1 day;
        set tmp_date = DATE_ADD(tmp_date, INTERVAL 1 DAY);
    END WHILE;
END$$

DELIMITER ;

call my_super_procedure("3dccd75a-4c8e-11e7-bf68-5ce0c56861d1", "2017-06-24 04:16:43", "2017-06-28 04:16:43");

I'm new in stored procedures. How can I fix this error?

Upvotes: 1

Views: 2433

Answers (1)

fancyPants
fancyPants

Reputation: 51878

Declare all variables at the beginning, not in the middle of your code.

DROP PROCEDURE IF EXISTS my_super_procedure;
DELIMITER $$
CREATE PROCEDURE my_super_procedure(IN in_id VARCHAR(255), IN in_from_date timestamp, IN in_to_date timestamp)
BEGIN
    DECLARE tmp_date timestamp;

    DROP TEMPORARY TABLE IF EXISTS tblResults;
    CREATE TEMPORARY TABLE IF NOT EXISTS tblResults(
        selected_date timestamp,
        total_value int
    );

    SET tmp_date = in_from_date;

    WHILE tmp_date < in_to_date DO

        SELECT count(*) as total_events FROM event e where e.scheduled between tmp_date and tmp_date + interval 1 day;
        set tmp_date = DATE_ADD(tmp_date, INTERVAL 1 DAY);
    END WHILE;
END$$

DELIMITER ;

Upvotes: 3

Related Questions