how to use a procedure in a SQL script?

Question

If i run every block of this script in mysql-query-browser, it works.

If i run it as mysql db < script.sql it gives me an error[1] that the documentation says it's for using reserved keywords.

Already tried to use ; everywhere.

-- table
CREATE TABLE `days` (
  `day` date NOT NULL,
  PRIMARY KEY  (`day`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- proc
CREATE PROCEDURE db.filldays()
BEGIN
  DECLARE v1 DATE DEFAULT '2039-01-01';
  WHILE v1 > '2009-01-01' DO
    INSERT days VALUES (v1);
    SET v1 =  DATE_SUB( v1, INTERVAL 1 DAY );
  END WHILE;
END

-- call proc
call filldays();

-- clean
drop procedure filldays;

[1] the erro: ERROR 1064 (42000) at line 8: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

Answer 1

you need to change DELIMITER before CREATE PROCEDURE and change it back to ; after .

DELIMITER $$

CREATE PROCEDURE db.filldays()
BEGIN
DECLARE v1 DATE DEFAULT '2039-01-01';
WHILE v1 > '2009-01-01' DO
INSERT days VALUES (v1);
SET v1 = DATE_SUB( v1, INTERVAL 1 DAY );
END WHILE;
END $$
DELIMITER ;