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

Question

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

score 1 · Answer 1 · answered Aug 02 '16 at 14:31

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.

Your approach is good but it is not working for me. Giving the wrong or null result. CALL `sitter_price2`(4, '1,2,3,4', 1, 5); - Giving the result if hours set >4 it is giving wrong result CALL `sitter_price2`(5, '1,2,3,4', 1, 5); - Giving the null — Harendra Singh, Aug 03 '16 at 04:26
All that matters is that the sql string it produces is exactly what it should. Then it executes it. So, one would work in Step 1 to get that right. When that is right, the problem is your data. — Drew, Aug 03 '16 at 04:51
Said another way, the EXECUTE performs the query you want. If wrong numbers are resulting, then your data is not what you expect. — Drew, Aug 03 '16 at 04:52

score 0 · Answer 2 · answered Aug 03 '16 at 04:31

0

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

answered Aug 03 '16 at 04:31

Harendra Singh

203
4
13

Yours would not use an index. Mine would. As I explained in [this other Answer](http://stackoverflow.com/a/38002986) – Drew Aug 03 '16 at 04:56
If you write code that ends up with `find_in_set()` as a solution, you might be doing something wrong and ought to ask around like on the Stack. Not that everyone sits around and optimizes things like I do. They might be happy to just tell you want you want to hear. – Drew Aug 03 '16 at 04:57
Drew why are you so angry. Your sp is not giving the correct result. your sp may be right. but not giving me results. leave your email id I will send tables with data then you can make your who is right you or me. – Harendra Singh Aug 03 '16 at 05:31
Who says I am angry. I am happily writing code for people. I enjoy it. – Drew Aug 03 '16 at 05:32
leave me your email id or where I give you tables ? – Harendra Singh Aug 03 '16 at 05:33
Drew I found the issue in your query this statement is not working - SET @theSql=CONCAT(@theSql,' where ka.age in(',age,') and start_hours > ',in_hours,' AND '); SET @theSql=CONCAT(@theSql,in_hours,'<= end_hours'); Working statement if we do like this SET @theSql=CONCAT(@theSql,' where ka.age in(',age,') and ' ,in_hours, ' between jp.start_hours and jp.end_hours '); – Harendra Singh Aug 03 '16 at 05:37
Sounds good. That is why I broke it out into Step 2 for you to confirm it and tweak if Step 1 was messed up. In the end, in will use an index. – Drew Aug 03 '16 at 05:43

score 0 · Answer 3 · answered Aug 03 '16 at 05:45

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

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

3 Answers3