Stored Procedure not behaving as expected
Posted by:
Kim Gaard
Date: June 09, 2015 11:12PM
I have created the following stored procedure that populates a Calendar table based on year entered in the parameters.
Issue is that no matter what I pas in as a Start and End Year the stored procedure seems to always execute as if @FromDate is always null.
Any ideas?
CREATE DEFINER=`Datagaard`@`%` PROCEDURE `FillCalendar`(IN `@FromYear` CHAR(4), IN `@ToYear` CHAR(4))
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE nDay INT;
DECLARE nDays INT;
DECLARE FromDate DATE;
DECLARE ToDate DATE;
DECLARE CurYear VARCHAR(4);
DECLARE CurMonth VARCHAR(25);
DECLARE CurDate DATE;
DECLARE DOW VARCHAR(25);
IF (@FromYear IS NULL) THEN
SET FromDate = CONCAT(CAST(DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -1 YEAR),'%Y') AS CHAR(4)) , '-01-01');
SET ToDate = CONCAT(CAST(DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 5 YEAR),'%Y') AS CHAR(4)) , '-12-31');
ELSE
SET FromDate :=CAST(CONCAT(@FromYear,'-01-01') AS DATE);
SET ToDate := CAST(CONCAT(@ToYear,'-12-31') AS DATE);
END IF;
SET nDay := 0;
SET nDays := DATEDIFF(ToDate, FromDate);
DELETE FROM calendar;
WHILE nDay <= nDays DO
-- Set Values
SET CurDate := DATE_ADD(FromDate, INTERVAL nDay DAY);
SET CurYear := date_format(CurDate,'%Y');
SET CurMonth := date_format(CurDate,'%M');
SET DOW := date_format(CurDate, '%W');
INSERT INTO calendar (calendaryear,calendarmonth,calendardow,calendardate,commencetime,ceasetime)
VALUES(CurYear, CurMonth, DOW, CurDate,'09:00','22:00');
SET nDay := nDay+1;
END WHILE;
END