How to use string in IN clause in stored procedure mysql

Question

In my code, I am passing a string variable('4,5,6') to my stored procedure which exploded from this string(4,5,6-Name). I am using IN clause in stored procedure and I know that IN clause accept ('4','5','6') or simply (4,5,6) this type of data but I am unable to use it in my stored procedure. please help me if there is any other way to achieve it.

CREATE DEFINER = 'root'@'localhost'
PROCEDURE kbm.report_site_transfer(IN siteid VARCHAR(50), IN datefrom VARCHAR (15), IN dateto VARCHAR (15))

SELECT
        * 
      FROM
        tbl_transfer
      WHERE
        tbl_transfer.siteNameID IN (siteid) AND (tbl_transfer.paymentDate BETWEEN datefrom AND dateto);

@Uchiha actually I am exploding a string (4,5,6-anyname) and exploded it with "-" seperator and want to use first value in IN clause (4,5,6) but when i checked the type in php, it is showing a string so i am unable to use in IN clause in stored procedure — Paramjeet
– Paramjeet, Commented Apr 18, 2016 at 12:32
See this about dynamically creating & executing a query string stackoverflow.com/questions/8549619/…. You would need to add the surrounding single quotes in the parameter you are passing. — PaulF
– PaulF, Commented Apr 18, 2016 at 12:46

Paramjeet · Accepted Answer · 2016-04-18 13:48:40Z

3

This code also working fine. I used FIND_IN_SET function :

FIND_IN_SET(tbl_transfer.siteNameID, siteid)

Full code :

CREATE DEFINER = 'root'@'localhost'
PROCEDURE kbm.report_site_transfer(IN siteid VARCHAR(50), IN datefrom VARCHAR (15), IN dateto VARCHAR (15))

SELECT
    * 
  FROM
    tbl_transfer
  WHERE
FIND_IN_SET(tbl_transfer.siteNameID, siteid)  AND (tbl_transfer.paymentDate BETWEEN datefrom AND dateto);

answered Apr 18, 2016 at 13:48

Paramjeet

3361 gold badge5 silver badges19 bronze badges

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

Comments

PaulF · Accepted Answer · 2016-04-18 13:03:09Z

1

Have you tried something like this as I gave you the link to - you create the entire query as a string & then execute as a prepared statement:

CREATE DEFINER = 'root'@'localhost'
PROCEDURE kbm.report_site_transfer(IN siteid VARCHAR(50), IN datefrom VARCHAR (15), IN dateto VARCHAR (15))

Set @query = CONCAT("SELECT * FROM tbl_transfer WHERE tbl_transfer.siteNameID IN (", siteid, ") AND (tbl_transfer.paymentDate BETWEEN ", datefrom, " AND ", dateto, ")";

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

If you wanted the single quoted version you could use this :

Set @query = CONCAT("SELECT * FROM tbl_transfer WHERE tbl_transfer.siteNameID IN ('", REPLACE(siteid,",", "','"), "') AND (tbl_transfer.paymentDate BETWEEN ", datefrom, " AND ", dateto, ")";

answered Apr 18, 2016 at 13:03

PaulF

6,7932 gold badges20 silver badges30 bronze badges

1 Comment

Paramjeet Over a year ago

Thanks PaulF, query running perfectly but I also found a way it's also giving the output as I wanted. but thank you so much for your efforts

Collectives™ on Stack Overflow

How to use string in IN clause in stored procedure mysql

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related