Creating Days in a Year - Stored Procedure
| From: | Pointmade_Noah1 | Date: | Tue, 23 Feb 2010 22:49:01 +0000 |
| Subject: | Creating Days in a Year - Stored Procedure | ||
| Groups: | php.general | ||
| Request: | Send a blank email to php-general+get-302323@lists.php.net to get a copy of this message | ||
I found this code below online and I am trying to modify it for my needs. I want to pass a YEAR (Int) to the stored procedure and have it create all the day in that passed year. With one tweak, If the day is Sunday the "sStatus" field will equal "Closed" ELSE "Open". All attempts come up with ERRORS. Also, I would like the sdate field to be a DATE result. Little help please? I appreciate it.
USE colombo;
-- this table will store dates sequenceCREATE TABLE seq_dates
(
sdate DATETIME NOT NULL,
sStatus VARCHAR NULL,
);
DROP PROCEDURE IF EXISTS colombo.sp_init_dates;
CREATE PROCEDURE colombo.sp_init_dates
(IN p_fdate DATETIME, IN p_tdate DATETIME)
BEGIN
DECLARE v_thedate DATETIME;
DECLARE v_Status VARCHAR;
TRUNCATE TABLE colombo.seq_dates;
SET v_thedate = p_fdate;
WHILE (v_thedate < p_tdate) DO
IF (DayName(v_theDate) = "Sunday" {
SET v_Status = "Closed";
}else{
SET v_Status = "Open";
} // I know this CODE is wrong
-- insert dates squence into seq_dates table
INSERT INTO seq_dates (sdate, sStatus)
VALUES (v_thedate, v_Status);
-- go to the next day
SET v_thedate = DATE_ADD(v_thedate, INTERVAL 1 DAY);
END WHILE;
END;