MySQL Forums
Forum List  »  Stored Procedures

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Stored Procedure not behaving as expected
2769
June 09, 2015 11:12PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.