CODEWITHSUNDEEP
mysql
Rober
Rober

Reputation: 6108

Mysql error: Error Code: 1292. Truncated incorrect DOUBLE value

I'm getting this error while executing this stored procedure in MySQL:

Error Code: 1292. Truncated incorrect DOUBLE value: 'WHERE s.name = 'Confirmed' AND bp.id = ''

DROP PROCEDURE IF EXISTS test;
DELIMITER //
CREATE PROCEDURE test (province VARCHAR(100), country VARCHAR(100), departureDate TIMESTAMP, arrivalDate TIMESTAMP, people INTEGER) 
BEGIN 
 DECLARE selectClause VARCHAR(500) DEFAULT "SELECT DISTINCT b.id, b.boat_model_id, b.port_id, b.address_id, b.maximum_discount, 
                 b.discounts_have_subtotals, b.skipper_type_id, b.length, b.people_capacity_id,
                 b.simple_cabin_id, b.double_cabin_id, b.bathroom_id, year_id ";         
 DECLARE fromClause VARCHAR(500) DEFAULT "FROM states s, boat_people bp, boat_prices bprd, boat_prices bpra, boats b ";
 DECLARE whereClause VARCHAR(1000) DEFAULT "WHERE s.name = 'Confirmed' 
 AND bp.id = " + people + "
 AND b.state_id = s.id 
 AND b.id NOT IN (SELECT bc.boat_id 
                  FROM boat_calendars bc 
                  WHERE (date(bc.since) <= " + departureDate + " AND date(bc.until) >= " + departureDate + ")   
                  OR (date(bc.since) <= " + arrivalDate + " AND date(bc.until) >= " + arrivalDate + "))
 AND bprd.boat_id = b.id 
 AND date(bprd.since) <= " + departureDate + " AND date(bprd.until) >= " + departureDate + "
 AND bpra.boat_id = b.id 
 AND date(bpra.since) <= " + arrivalDate + " AND date(bpra.until) >= " + arrivalDate + "
 AND truncate(((bprd.weekly_price + bpra.weekly_price)/2), 2)  > 0";
 SET @queryClause = concat(selectClause, fromClause, whereClause);
 PREPARE stmt FROM @queryClause;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
END // 
CALL test('Mallorca (Islas Baleares)', 'España', '2016-03-02 16:02:12', '2016-03-02 16:02:12', 1);

Table boat_people.id is INT(11).

Upvotes: 0

Views: 2213

Answers (1)

Tin
Tin

Reputation: 804

Is there any specific reason why you "DECLARE" variable? You can just write "SELECT" query directly. For example,

CREATE PROCEDURE `myprocedure`(myparam VARCHAR(100))
BEGIN
   SELECT * from mytable where myfield = myparam;
END

CALL myprocedure('value');

EDIT: Basically + doesn't work for string concat,

CREATE PROCEDURE `myprocedure`(myparam VARCHAR(100))
BEGIN
    DECLARE queryClause VARCHAR(1000) DEFAULT CONCAT('select * from mytable where myfield = "', myparam, '"');
    SET @queryA = queryClause;
    PREPARE stmt FROM @queryA;
    EXECUTE stmt;
END

You don't need "DECLARE". You can just use SET as follow:

SET @queryA = CONCAT('select * from mytable where myfield = "', myparam, '"');

Or multiple SET as follow:

SET @queryA = CONCAT('select * from mytable where myfield = "', myparam, '"');
SET @queryB = ' and myfield is not null ';
SET @queryC = CONCAT(@queryA, @queryB);

Upvotes: 1

Related Questions