Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
618 views
in Technique[技术] by (71.8m points)

mysql - CONCAT in stored procedure returns null

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...