-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`advancebooker`@`%` PROCEDURE `make_intervals`(startdate datetime, enddate datetime, intval integer, unitval varchar(10), tmpTableName varchar(100))
BEGIN
-- *************************************************************************
--
-- Description:
-- This procedure creates a temporary table named time_intervals with the
-- interval_start and interval_end fields specifed from the startdate and
-- enddate arguments, at intervals of intval (unitval) size.
-- *************************************************************************
declare thisDate datetime;
declare nextDate datetime;
declare id INT;
declare vehicleCount int default 0;
declare productId int default 0;
set thisDate = startdate;
-- *************************************************************************
-- Drop / create the table
-- *************************************************************************
SET @sql = CONCAT('CREATE TABLE IF NOT EXISTS ',tmpTableName, '(id INT(11) NOT NULL AUTO_INCREMENT, interval_start DATETIME, interval_end DATETIME, vehicleCount INT(20), productId INT(10), PRIMARY KEY (id))');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- *************************************************************************
-- Loop through the startdate adding each intval interval until enddate
-- *************************************************************************
repeat
select
case unitval
when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
when 'SECOND' then timestampadd(SECOND, intval, thisDate)
when 'MINUTE' then timestampadd(MINUTE, intval, thisDate)
when 'HOUR' then timestampadd(HOUR, intval, thisDate)
when 'DAY' then timestampadd(DAY, intval, thisDate)
when 'WEEK' then timestampadd(WEEK, intval, thisDate)
when 'MONTH' then timestampadd(MONTH, intval, thisDate)
when 'QUARTER' then timestampadd(QUARTER, intval, thisDate)
when 'YEAR' then timestampadd(YEAR, intval, thisDate)
end into nextDate;
SET @sql = CONCAT("INSERT INTO ",tmpTableName," SELECT ", id, thisDate,TIMESTAMPADD(MICROSECOND, -1, nextDate), vehicleCount , productId);
SET thisDate = nextDate;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
until thisDate >= enddate
end repeat;
END
When i debug the above stored proc in the SET @sql = CONCAT("INSERT INTO ",tmpTableName," ... line I am getting null value?
Please let me know where the error is ? and the error seems to be in the first line Error Code: 1064. 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 'NULL' at line 1
