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

Drew · Accepted Answer · 2016-08-02 14:31:39Z

1

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.

answered Aug 2, 2016 at 14:31

Drew

25k10 gold badges47 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Harendra Singh Over a year ago

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

Drew Over a year ago

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 Over a year ago

Said another way, the EXECUTE performs the query you want. If wrong numbers are resulting, then your data is not what you expect.

Harendra Singh · Accepted Answer · 2016-08-03 04:31:18Z

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 3, 2016 at 4:31

Harendra Singh

2034 silver badges13 bronze badges

7 Comments

Drew Over a year ago

Yours would not use an index. Mine would. As I explained in this other Answer

Drew Over a year ago

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.

Harendra Singh Over a year ago

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.

Drew Over a year ago

Who says I am angry. I am happily writing code for people. I enjoy it.

Harendra Singh Over a year ago

leave me your email id or where I give you tables ?

|

Harendra Singh · Accepted Answer · 2016-08-03 05:45:10Z

0

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

answered Aug 3, 2016 at 5:45

Harendra Singh

2034 silver badges13 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related