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 ;

Answer 1

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

There are other issues:

1. MySQL doesn't have table variables. Use a temporary table for this.
2. You don't need to declare user variables that begin with @. Make them ordinary variables.
3. To insert a value from a variable, use VALUES rather than SELECT.
4. 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 ;