MySQL error "Truncated incorrect DOUBLE value" in procedure

Question

I have problem with my procedure. I try to take values from sales table and make a query using them. Procedure looks like this:

DROP PROCEDURE IF EXISTS turnover;

DELIMITER $$

CREATE PROCEDURE turnover()
BEGIN
    DECLARE col INT;
    DECLARE q TEXT;
    DECLARE i INT DEFAULT 0;
    DECLARE m TEXT;
    SET col = (SELECT count(DISTINCT article) FROM sales);
    SET q = "SELECT article, ";
    WHILE i < co DO
        SET m = (SELECT DISTINCT month FROM sales LIMIT 1 OFFSET i);
        SET q = q + "SUM(IF(month=" + m + ",value,NULL)) AS " + m;
        IF i < (col - 1) THEN
            SET q = q + ", ";
        END IF;
        SET i = i + 1;
    END WHILE;
    SET q = q + " FROM sales GROUP BY article";
    EXECUTE q;
END$$

DELIMITER ;

CALL turnover();

I receive error:

Error Code: 1292. Truncated incorrect DOUBLE value: ',value,NULL)) AS '

How i can make it works?

Thanks.

not sure if this is your problem, but should this line WHILE i < co DO be WHILE i < col DO? The error usually happens when you try to compare a string to an int somewhere in your query. — JanR
It's pretty hard to tell with this. Why don't you log the generated statement? BTW, this probably doesn't even a need a stored procedure. — e4c5
@e4c5 that is a fair comment. For educational purposes I plopped in the below answer regardless of it perhaps being an XY Problem. — Drew

Drew Drew · Accepted Answer · 2016-11-23T00:45:28

The col issue was fixed or assumed in the below.

CREATE SCHEMA safe_Tuesday_01; -- safe sandbox
USE safe_Tuesday_01;    -- DO the work in this db to test it

-- a fake table, we need something
create table sales 
(   article varchar (100) not null,
    month int not null
);

Step 1, find out what the string looks like:

DROP PROCEDURE IF EXISTS turnover;
DELIMITER $$
CREATE PROCEDURE turnover()
BEGIN
    DECLARE col INT;
    DECLARE q TEXT;
    DECLARE i INT DEFAULT 0;
    DECLARE m TEXT;
    SET col = (SELECT count(DISTINCT article) FROM sales);
    SET q = "SELECT article, ";
    WHILE i < col DO
        SET m = (SELECT DISTINCT month FROM sales LIMIT 1 OFFSET i);
        SET q = CONCAT(q,"SUM(IF(month=" + m + ",value,NULL)) AS ", m);
        IF i < (col - 1) THEN
            SET q = q + ", ";
        END IF;
        SET i = i + 1;
    END WHILE;
    SET q = CONCAT(q," FROM sales GROUP BY article");
    select q;
    -- EXECUTE q; -- No no no this is wrong anyway
END$$
DELIMITER ;

CALL turnover();

SELECT article, FROM sales GROUP BY article

Well that above SELECT does not look so hot. Repeatedly fix your logic in Step 1 to fix that string.

Step 2, when you fix the code above, plop in the below. Note, at the moment, it is not fixed. So do that, again, above.

But in the below, use a proper PREPARED STATEMENT which you are not.

DROP PROCEDURE IF EXISTS turnover;
DELIMITER $$
CREATE PROCEDURE turnover()
BEGIN
    DECLARE col INT;
    DECLARE q TEXT;
    DECLARE i INT DEFAULT 0;
    DECLARE m TEXT;
    SET col = (SELECT count(DISTINCT article) FROM sales);
    SET q = "SELECT article, ";
    WHILE i < col DO
        SET m = (SELECT DISTINCT month FROM sales LIMIT 1 OFFSET i);
        SET q = CONCAT(q,"SUM(IF(month=" + m + ",value,NULL)) AS ", m);
        IF i < (col - 1) THEN
            SET q = q + ", ";
        END IF;
        SET i = i + 1;
    END WHILE;
    SET q = CONCAT(q," FROM sales GROUP BY article");
    -- select q;
    SET @theSQL=q;
    PREPARE stmt1 FROM @theSQL;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;

When done,

DROP SCHEMA safe_Tuesday_01; -- clean up, poof, sandbox is gone

CONCAT is your friend. You missed this step. It is important that the PREPARE works against a User Variable (with an @ sign) and not a Local Var (from a DECLARE) else it will blow up. So I fixed that above with the @theSQL

Again, see the MySQL Manual Page PREPARE Syntax. It is important to get your string right. That is the point of Step 1. Only then do you move on to Step 2 and using it.

MySQL error "Truncated incorrect DOUBLE value" in procedure

2 Answers