CODEWITHSUNDEEP
mysqlstored-procedures
Harendra Singh
Harendra Singh

Reputation: 203

mysql procedure - issue with parsing parameter in where condition in clause

below is the procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `sitter_price`(
   in in_hours float,
   in age varchar(100),
   in no_of_sitters int,
   in no_of_days int
)
BEGIN
  select 
  TRUNCATE(sum(price)*in_hours*no_of_days*no_of_sitters,2) as 
  total_amount 
    from job_prices jp
    join kids_ages ka on ka.id = jp.kids_age_id
where ka.age in(age) and start_hours > in_hours 
    AND in_hours <= end_hours;
END

The problem is in this procedure is how I will pass in age varchar(100), parameter in, in clause currently I am parsing using the query 

CALL `usitterz`.`sitter_price`(4.10,'1,2,3,4', 3, 5);

but this is wrong because in query read this like in('1,2,3,4') but I want it like - in(1,2,3,4).

it will be like 

CREATE DEFINER=`root`@`localhost` PROCEDURE `sitter_price`(
   in in_hours float,
   in age varchar(100),
   in no_of_sitters int,
   in no_of_days int
)
BEGIN
  select 
  TRUNCATE(sum(price)*in_hours*no_of_days*no_of_sitters,2) as 
  total_amount 
    from job_prices jp
    join kids_ages ka on ka.id = jp.kids_age_id
where ka.age in(1,2,3,4) and start_hours > in_hours 
    AND in_hours <= end_hours;
END

Upvotes: 0

Views: 186

Answers (3)

Harendra Singh
Harendra Singh

Reputation: 203

Another answer 

CREATE DEFINER=`root`@`localhost` PROCEDURE `sitter_price`(
   in in_hours float,
   in age varchar(100),
   in no_of_sitters int,
   in no_of_days int
)
BEGIN
    SET @theSql=CONCAT('SELECT TRUNCATE(sum(price)*',in_hours,'*',no_of_days,'*',no_of_sitters,',2)');
    SET @theSql=CONCAT(@theSql,' as total_amount from job_prices jp join kids_ages ka on ka.id = jp.kids_age_id');
    SET @theSql=CONCAT(@theSql,' where ka.age in(',age,') and ' ,in_hours, ' between jp.start_hours and jp.end_hours ');      
    PREPARE stmt from @theSql; -- create a prepared stmt from the above concat
    EXECUTE stmt;   -- run it
    DEALLOCATE PREPARE stmt;    -- cleanup
END

Upvotes: 0

Harendra Singh
Harendra Singh

Reputation: 203

Below is the answer of my own question

CREATE PROCEDURE `sitter_price`(
   in in_hours float,
   in age varchar(100),
   in no_of_sitters int,
   in no_of_days int
)
BEGIN
 select 
  TRUNCATE(sum(price)*in_hours*no_of_days*no_of_sitters,2) as total_amount
  from job_prices jp
  join kids_ages ka on ka.id = jp.kids_age_id
 where 
  jp.status = 1 and 
  find_in_set(ka.age,age) and
  in_hours between jp.start_hours and jp.end_hours;
END

Call procedure 

CALL `usitterz`.`sitter_price`(6, '1,2,3,4', 1, 5);

Upvotes: 0

Drew
Drew

Reputation: 24960

Step1: mess around to get the string right for an EXECUTE

DROP PROCEDURE IF EXISTS sitter_price;
DELIMITER $
CREATE DEFINER=`root`@`localhost` PROCEDURE `sitter_price`(
   in in_hours float,
   in age varchar(100),
   in no_of_sitters int,
   in no_of_days int
)
BEGIN
    SET @theSql=CONCAT('SELECT TRUNCATE(sum(price)*',in_hours,'*',no_of_days,'*',no_of_sitters,',2)');
    SET @theSql=CONCAT(@theSql,' as total_amount from job_prices jp join kids_ages ka on ka.id = jp.kids_age_id');
    SET @theSql=CONCAT(@theSql,' where ka.age in(',age,') and start_hours > ',in_hours,' AND ');
    SET @theSql=CONCAT(@theSql,in_hours,'<= end_hours');
    /*
  select 
  TRUNCATE(sum(price)*in_hours*no_of_days*no_of_sitters,2) as 
  total_amount 
    from job_prices jp
    join kids_ages ka on ka.id = jp.kids_age_id
where ka.age in(1,2,3,4) and start_hours > in_hours 
    AND in_hours <= end_hours;
    */
    select @theSql;
END$
DELIMITER ;

Step2: pass in parameter to see what string looks like

call sitter_price(89,'1,2,4,8',11,12);

SELECT TRUNCATE(sum(price)*89*12*11,2) as total_amount 
from job_prices jp 
join kids_ages ka on ka.id = jp.kids_age_id 
where ka.age in(1,2,4,8) and start_hours > 89 
AND 89<= end_hours

Step3: Finalize the Stored Proc with PREPARE and EXECUTE it.

DROP PROCEDURE IF EXISTS sitter_price;
DELIMITER $
CREATE DEFINER=`root`@`localhost` PROCEDURE `sitter_price`(
   in in_hours float,
   in age varchar(100),
   in no_of_sitters int,
   in no_of_days int
)
BEGIN
    SET @theSql=CONCAT('SELECT TRUNCATE(sum(price)*',in_hours,'*',no_of_days,'*',no_of_sitters,',2)');
    SET @theSql=CONCAT(@theSql,' as total_amount from job_prices jp join kids_ages ka on ka.id = jp.kids_age_id');
    SET @theSql=CONCAT(@theSql,' where ka.age in(',age,') and start_hours > ',in_hours,' AND ');
    SET @theSql=CONCAT(@theSql,in_hours,'<= end_hours');
    PREPARE stmt from @theSql; -- create a prepared stmt from the above concat
    EXECUTE stmt;   -- run it
    DEALLOCATE PREPARE stmt;    -- cleanup
END$
DELIMITER ;

MySqL Manual Page SQL Syntax for Prepared Statements.

Note, the above CONCAT() will only be successful with a User Variable (with an @ sign). Not a Local Variable with a DECLARE.

Upvotes: 1

Related Questions