DECLARE is not valid at this position, expecting EOF

Question

I am a novice in SQL and I was trying to generate dates within 2 dates that are prespecified by me. I was using the code below, but I get an error in MySQL workbench stating that 'DECLARE is not valid at this position'... What am I doing wrong?

DELIMITER $$
CREATE PROCEDURE proc()
BEGIN

DECLARE @dates TABLE(dt datetime)    
DECLARE @dateFrom datetime
DECLARE @dateTo datetime

SET @dateFrom = '2001/01/01'
SET @dateTo = '2001/01/12'


WHILE(@dateFrom < @dateTo)
BEGIN
   SELECT @dateFrom = DATEADD(day, 1,@dateFrom)
   INSERT INTO @dates 
   SELECT @dateFrom
END
END$$

DELIMITER ;

Does this answer your question? [How to declare a variable in MySQL?](https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql) — P.Salmon, Jun 05 '20 at 14:17

score 5 · Accepted Answer · answered Jun 05 '20 at 14:19

The syntax error is because you forgot the ; after each statement.

There are other issues:

MySQL doesn't have table variables. Use a temporary table for this.
You don't need to declare user variables that begin with @. Make them ordinary variables.
To insert a value from a variable, use VALUES rather than SELECT.
DATEADD is not a MySQL function. It has DATE_ADD, but the syntax is different from what you used.

DELIMITER $$
CREATE PROCEDURE proc()
BEGIN

DECLARE dateFrom datetime;
DECLARE dateTo datetime;

SET dateFrom = '2001-01-01';
SET dateTo = '2001-01-12';

CREATE TEMPORARY TABLE dates (
    dt datetime
);

WHILE(dateFrom < dateTo)
BEGIN
   SET dateFrom = DATE_ADD(dateFrom, INTERVAL 1 DAY);
   INSERT INTO dates VALUES (dateFrom);
END WHILE;
END$$

DELIMITER ;

DECLARE is not valid at this position, expecting EOF

1 Answers1