mysql - CONCAT in stored procedure returns null

Question

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

Answer 1

The documentation says

CONCAT() returns NULL if any argument is NULL.

This is demonstrated by this example: http://sqlfiddle.com/#!2/d41d8/47037

You can find some documentation on working with nulls here: http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html