mysql stored procedure syntax errors with dates

Question

Hi I am having fun trying to get a stored procedure to parse correctly in MySQL.

My issue is with dates. I am trying to get the store procedure to create a start date that is the beginning of the current month e.g. 2009-07-01. Using this date I then use the DATA_ADD() function to add a month so that it reads 2009-08-01.

However my problem is that when I try and run the procdure to create it I get the following error:

Script line: 7  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 'DECLARE 
cur_month INT;
SET cur_month = (SELECT MONTH(CURRENT_DATE()));

The code for the store procedure looks like this:

DROP PROCEDURE IF EXISTS sp_test;

DELIMITER //

CREATE PROCEDURE sp_test()
BEGIN

  /*we work out the start and end dates*/
  DECLARE cur_year INT;
  SET cur_year = (SELECT YEAR(CURRENT_DATE()));

  DECLARE cur_month INT;
  SET cur_month = (SELECT MONTH(CURRENT_DATE()));


  DECLARE temp_date VARCHAR(10);
  SET temp_date = (SELECT CONCAT(cur_year,'-',cur_month,'-01'));

  DECLARE start_date DATE;
  SET start_date = (SELECT CAST(temp_date AS DATE)));

  DECLARE end_date DATE;
  SET end_date = (SELECT DATE_ADD(start_date, INTERVAL 1 MONTH));


  INSERT INTO my_table (startdate, enddate)VALUES(start_date, end_date);

 END; //

DELIMITER ;

I've run the queries independantly and they all return correct values and work, it only starts to fail with syntax errors when I add them into a stored procedure.

What am I missing here that is causing all my head aches?

Thanks...

Answer 1

Do variables in SQL not require @ i.e. @end_date, @start_date

Edit

try:

DECLARE cur_year, cur_month INT;
DECLARE temp_date VARCHAR(10);
DECLARE start_date, end_date DATE;

SET cur_year = (SELECT YEAR(CURRENT_DATE()));
SET cur_month = (SELECT MONTH(CURRENT_DATE()));
SET temp_date = (SELECT CONCAT(cur_year,'-',cur_month,'-01'));
SET start_date = (SELECT CAST(temp_date AS DATE)));
SET end_date = (SELECT DATE_ADD(start_date, INTERVAL 1 MONTH));

INSERT INTO my_table(startdate, enddate) VALUES(start_date, end_date);

Answer 2

I've tried your example, it works in my MySQL 5.0.32 :

delimiter //
CREATE PROCEDURE sp_test()
BEGIN
  DECLARE cur_year INT;
  SET cur_year = (SELECT YEAR(CURRENT_DATE()));
  INSERT INTO tt (y) VALUES (cur_year);
END; //
delimiter ;

CREATE TABLE tt (y INT);
CALL sp_test();
Query OK, 1 row affected (0.00 sec)

You can try rewrite SET as

SELECT YEAR(CURRENT_DATE()) INTO cur_year;

Will it work? What's your MySQL version?..

Answer 3

You need to DECLARE all variables at the start of the BEGIN - END block. You also have a mismatched parentheses (extra close) in your CAST line. The following should work:

DROP PROCEDURE IF EXISTS sp_test;

DELIMITER //

CREATE PROCEDURE sp_test()
BEGIN

  /*we work out the start and end dates*/
  DECLARE cur_year INT;
  DECLARE cur_month INT;
  DECLARE temp_date VARCHAR(10);
  DECLARE start_date DATE;
  DECLARE end_date DATE;

  SET cur_year = (SELECT YEAR(CURRENT_DATE()));

  SET cur_month = (SELECT MONTH(CURRENT_DATE()));

  SET temp_date = (SELECT CONCAT(cur_year,'-',cur_month,'-01'));
  SET start_date = (SELECT CAST(temp_date AS DATE));

  SET end_date = (SELECT DATE_ADD(start_date, INTERVAL 1 MONTH));
  INSERT INTO my_table (startdate, enddate)VALUES(start_date, end_date);


 END; //

DELIMITER ;

Answer 4

You have to declare all variables before you start to assign values to them, the following code seems to work for me:

`DELIMITER $$

DROP PROCEDURE IF EXISTS test.sp_test$$ CREATE PROCEDURE test.sp_test () BEGIN

/we work out the start and end dates/ DECLARE cur_year INT; DECLARE cur_month INT; DECLARE temp_date VARCHAR(10); DECLARE start_date DATE; DECLARE end_date DATE;

SET cur_year = (SELECT YEAR(CURRENT_DATE()));

SET cur_month = (SELECT MONTH(CURRENT_DATE()));

SET temp_date = (SELECT CONCAT(cur_year,'-',cur_month,'-01'));

SET start_date = (SELECT CAST(temp_date AS DATE));

SET end_date = (SELECT DATE_ADD(start_date, INTERVAL 1 MONTH));

INSERT INTO alex.my_table (startdate, enddate)VALUES(start_date, end_date);

END$$

DELIMITER ;`