Mysql Prepared Statement with dynamic column based on Date

Question

Hi I have a prepared statement inside a stored procedure with dynamic generation of columns based on Creation Date. The problem is when I execute my stored procedure once its okay and running well. But when i change its parameter values. It gets Error 1054 Having Unknown column example 'December'. I've read some article about it. And it says "prepared statement is a feature used to execute the same (or similar) SQL statements repeatedly with high efficiency."? Here's the code. Thanks. Please disregard other parameters you'll see as it was edited and does not affect the result.

CREATE DEFINER=`root`@`%` PROCEDURE `MyStoredProcedure`(
IN `_PageIndex` INT, 
`_PageSize` INT, 
 OUT `_RecordCount` INT,
 _DateCreatedFrom VARCHAR(50),
 _DateCreatedTo VARCHAR(50)

) BEGIN

DECLARE _Jan INT;
DECLARE _Feb INT;
DECLARE _Mar INT;
DECLARE _Apr INT;
DECLARE _May INT;
DECLARE _Jun INT;
DECLARE _Jul INT;
DECLARE _Aug INT;
DECLARE _Sep INT;
DECLARE _Oct INT;
DECLARE _Nov INT;
DECLARE _Dec INT;
DECLARE _sql VARCHAR(65535);
DECLARE _sql_dynamic VARCHAR(65535);
DECLARE _GrandTotal VARCHAR(65535);
DECLARE _offset INT;
DECLARE _Year VARCHAR(50);
DROP TEMPORARY TABLE IF EXISTS Results;

SET _Jan = 1;
SET _Feb = 2;
SET _Mar = 3;
SET _Apr = 4;
SET _May = 5;
SET _Jun = 6;
SET _Jul = 7;
SET _Aug = 8;
SET _Sep = 9;
SET _Oct = 10;
SET _Nov = 11;
SET _Dec = 12;
SET _Year = YEAR(CAST(_DateCreatedFrom AS DATE));
SET _DateCreatedFrom = MONTH(CAST(_DateCreatedFrom AS DATE));
SET _DateCreatedTo = MONTH(CAST(_DateCreatedTo AS DATE));
SET _sql = '';
SET _sql_dynamic = '';
SET _GrandTotal = '';
SET @stmt_str = '';


SET _offset = (_PageIndex - 1) * _PageSize;
SET @rownum=0;

SET _sql:= CONCAT('CREATE TEMPORARY TABLE Results ');

SET _sql:= CONCAT(_sql,'SELECT @rownum:=@rownum+1 AS ''#'', a.*, (');


IF(_Jan BETWEEN _DateCreatedFrom AND _DateCreatedTo) THEN
    SET _sql_dynamic:= CONCAT('COUNT(IF(MONTH(tr.`DateCreated`) = 1 AND YEAR(tr.`DateCreated`) = ', _Year ,', 1, null)) AS January,');
    SET _GrandTotal := CONCAT(_GrandTotal,'January + ');
        IF(_DateCreatedTo = 1) THEN
            SET _sql_dynamic:= SUBSTRING(_sql_dynamic,1,LENGTH(_sql_dynamic) - 1);
            SET _GrandTotal := SUBSTRING(_GrandTotal,1,LENGTH(_GrandTotal) - 2);
        END IF;

END IF;

IF(_Feb BETWEEN _DateCreatedFrom AND _DateCreatedTo) THEN
    SET _sql_dynamic:= CONCAT(_sql_dynamic,'COUNT(IF(MONTH(tr.`DateCreated`) = 2 AND YEAR(tr.`DateCreated`) = ', _Year ,', 1, null)) AS February,');
    SET _GrandTotal := CONCAT(_GrandTotal,'February + ');
    IF(_DateCreatedTo = 2) THEN
            SET _sql_dynamic:= SUBSTRING(_sql_dynamic,1,LENGTH(_sql_dynamic) - 2);
            SET _GrandTotal := SUBSTRING(_GrandTotal,1,LENGTH(_GrandTotal) - 2);
    END IF;

END IF;

IF(_Mar BETWEEN _DateCreatedFrom AND _DateCreatedTo) THEN
    SET _sql_dynamic:= CONCAT(_sql_dynamic,'COUNT(IF(MONTH(tr.`DateCreated`) = 3 AND YEAR(tr.`DateCreated`) = ', _Year ,', 1, null)) AS March,');
    SET _GrandTotal := CONCAT(_GrandTotal,'March + ');
    IF(_DateCreatedTo = 3) THEN
            SET _sql_dynamic:= SUBSTRING(_sql_dynamic,1,LENGTH(_sql_dynamic) - 2);
            SET _GrandTotal := SUBSTRING(_GrandTotal,1,LENGTH(_GrandTotal) - 2);
    END IF;

END IF;

IF(_Apr BETWEEN _DateCreatedFrom AND _DateCreatedTo) THEN
    SET _sql_dynamic:= CONCAT(_sql_dynamic,'COUNT(IF(MONTH(tr.`DateCreated`) = 4 AND YEAR(tr.`DateCreated`) = ', _Year ,', 1, null)) AS April,');
    SET _GrandTotal := CONCAT(_GrandTotal,'April + ');
    IF(_DateCreatedTo = 4) THEN
            SET _sql_dynamic:= SUBSTRING(_sql_dynamic,1,LENGTH(_sql_dynamic) - 2);
            SET _GrandTotal := SUBSTRING(_GrandTotal,1,LENGTH(_GrandTotal) - 2);
    END IF;

END IF;

IF(_May BETWEEN _DateCreatedFrom AND _DateCreatedTo) THEN
    SET _sql_dynamic:= CONCAT(_sql_dynamic,'COUNT(IF(MONTH(tr.`DateCreated`) = 5 AND YEAR(tr.`DateCreated`) = ', _Year ,', 1, null)) AS May,');
    SET _GrandTotal := CONCAT(_GrandTotal,'May + ');
    IF(_DateCreatedTo = 5) THEN
            SET _sql_dynamic:= SUBSTRING(_sql_dynamic,1,LENGTH(_sql_dynamic) - 2);
            SET _GrandTotal := SUBSTRING(_GrandTotal,1,LENGTH(_GrandTotal) - 2);
        END IF;

END IF;

IF(_Jun BETWEEN _DateCreatedFrom AND _DateCreatedTo) THEN
    SET _sql_dynamic:= CONCAT(_sql_dynamic,'COUNT(IF(MONTH(tr.`DateCreated`) = 6 AND YEAR(tr.`DateCreated`) = ', _Year ,', 1, null)) AS June,');
    SET _GrandTotal := CONCAT(_GrandTotal,'June + ');
    IF(_DateCreatedTo = 6) THEN
            SET _sql_dynamic:= SUBSTRING(_sql_dynamic,1,LENGTH(_sql_dynamic) - 2);
            SET _GrandTotal := SUBSTRING(_GrandTotal,1,LENGTH(_GrandTotal) - 2);
        END IF;

END IF;

IF(_Jul BETWEEN _DateCreatedFrom AND _DateCreatedTo) THEN
    SET _sql_dynamic:= CONCAT(_sql_dynamic,'COUNT(IF(MONTH(tr.`DateCreated`) = 7 AND YEAR(tr.`DateCreated`) = ', _Year ,', 1, null)) AS July,');
    SET _GrandTotal := CONCAT(_GrandTotal,'July + ');
    IF(_DateCreatedTo = 7) THEN
            SET _sql_dynamic:= SUBSTRING(_sql_dynamic,1,LENGTH(_sql_dynamic) - 2);
            SET _GrandTotal := SUBSTRING(_GrandTotal,1,LENGTH(_GrandTotal) - 2);
        END IF;


END IF;

IF(_Aug BETWEEN _DateCreatedFrom AND _DateCreatedTo) THEN
    SET _sql_dynamic:= CONCAT(_sql_dynamic,'COUNT(IF(MONTH(tr.`DateCreated`) = 8 AND YEAR(tr.`DateCreated`) = ', _Year ,', 1, null)) AS August,');
    SET _GrandTotal := CONCAT(_GrandTotal,'August + ');
    IF(_DateCreatedTo = 8) THEN
            SET _sql_dynamic:= SUBSTRING(_sql_dynamic,1,LENGTH(_sql_dynamic) - 2);
            SET _GrandTotal := SUBSTRING(_GrandTotal,1,LENGTH(_GrandTotal) - 2);
        END IF;

END IF;

IF(_Sep BETWEEN _DateCreatedFrom AND _DateCreatedTo) THEN
    SET _sql_dynamic:= CONCAT(_sql_dynamic,'COUNT(IF(MONTH(tr.`DateCreated`) = 9 AND YEAR(tr.`DateCreated`) = ', _Year ,', 1, null)) AS September,');
    SET _GrandTotal := CONCAT(_GrandTotal,'September + ');
    IF(_DateCreatedTo = 9) THEN
            SET _sql_dynamic:= SUBSTRING(_sql_dynamic,1,LENGTH(_sql_dynamic) - 2);
            SET _GrandTotal := SUBSTRING(_GrandTotal,1,LENGTH(_GrandTotal) - 2);
        END IF;

END IF;

IF(_Oct BETWEEN _DateCreatedFrom AND _DateCreatedTo) THEN
    SET _sql_dynamic:= CONCAT(_sql_dynamic,'COUNT(IF(MONTH(tr.`DateCreated`) = 10 AND YEAR(tr.`DateCreated`) = ', _Year ,', 1, null)) AS October,');
    SET _GrandTotal := CONCAT(_GrandTotal,'October + ');
    IF(_DateCreatedTo = 10) THEN
            SET _sql_dynamic:= SUBSTRING(_sql_dynamic,1,LENGTH(_sql_dynamic) - 2);
            SET _GrandTotal := SUBSTRING(_GrandTotal,1,LENGTH(_GrandTotal) - 2);
        END IF;

END IF;

IF(_Nov BETWEEN _DateCreatedFrom AND _DateCreatedTo) THEN
    SET _sql_dynamic:= CONCAT(_sql_dynamic,'COUNT(IF(MONTH(tr.`DateCreated`) = 11 AND YEAR(tr.`DateCreated`) = ', _Year ,', 1, null)) AS November,');
    SET _GrandTotal := CONCAT(_GrandTotal,'November + ');
    IF(_DateCreatedTo = 11) THEN
            SET _sql_dynamic:= SUBSTRING(_sql_dynamic,1,LENGTH(_sql_dynamic) - 2);
            SET _GrandTotal := SUBSTRING(_GrandTotal,1,LENGTH(_GrandTotal) - 2);
    END IF;

END IF;

IF(_Dec BETWEEN _DateCreatedFrom AND _DateCreatedTo) THEN
    SET _sql_dynamic:= CONCAT(_sql_dynamic,'COUNT(IF(MONTH(tr.`DateCreated`) = 12 AND YEAR(tr.`DateCreated`) = ', _Year ,', 1, null)) AS December ');
    SET _GrandTotal := CONCAT(_GrandTotal,'+ December ');
    IF(_DateCreatedTo = 12) THEN
            SET _sql_dynamic:= SUBSTRING(_sql_dynamic,1,LENGTH(_sql_dynamic) - 2);
            SET _GrandTotal := CONCAT('December ');
        END IF;


END IF;


SET _GrandTotal := CONCAT(_GrandTotal,') ''GRAND TOTAL'' ');
SET _sql := CONCAT(_sql,_GrandTotal);
SET _sql := CONCAT(_sql,'FROM( SELECT IFNULL(Problem, ''GRAND TOTAL'') Problem, ');
SET _sql := CONCAT(_sql,_sql_dynamic, ' FROM trservices  tr
                                  LEFT JOIN libproblems lp on tr.ProblemID=lp.ID
                                  GROUP BY lp.Problem WITH ROLLUP) a;');
SET @stmt_str = _sql;
PREPARE stmt FROM @stmt_str;    
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


SET _RecordCount =(SELECT COUNT(*) FROM Results);

SELECT * FROM Results
LIMIT _offset, _PageSize;

DROP TEMPORARY TABLE Results;

END

Heres the image for my output

Output 1st Run

Output 2nd Run changed TO Date

Once you should check `_DateCreatedFrom AND _DateCreatedTo` what is the exact value in your second case — er.irfankhan11, Feb 08 '18 at 06:22
When i select the concatenated query before going into prepared statements. I get January to November. — PapaP, Feb 08 '18 at 08:28
I've run the query without "Create temporary table if exists results". My query displays. But i need to shoot the data into temporary table in order to make my Pagination work — PapaP, Feb 09 '18 at 06:59
I think you can use limit in your main query, hope it will work — er.irfankhan11, Feb 09 '18 at 08:52
Thanks Irfan. Upon searching its a bug issue due to mysql version. My query will work on mysql 5.6 above my mysql server is 5.5. by the way thanks for the help. — PapaP, Feb 09 '18 at 09:23
Great!, I am too debug your query and not getting any issue. — er.irfankhan11, Feb 09 '18 at 09:30

Mysql Prepared Statement with dynamic column based on Date

0 Answers0