CODEWITHSUNDEEP
mysqlstored-procedures
Grant Collins
Grant Collins

Reputation: 1791

mysql stored procedure syntax errors with dates

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...

Upvotes: 1

Views: 17525

Answers (3)

Alexander Kjäll
Alexander Kjäll

Reputation: 4336

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 ;`

Upvotes: 0

Nick Fortescue
Nick Fortescue

Reputation: 44173

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 ;

Upvotes: 2

Alexander Azarov
Alexander Azarov

Reputation: 13221

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?..

Upvotes: 1

Related Questions