MySQL Query – Write Correct Array String Parameter

arrayMySQLparameterstring

I'm trying to put up a correct string to pass as a parameter to a simple query.

Correct query that returns records:

SELECT id_s FROM group_of_id_s
WHERE date IN ('2020-08-01','2020-08-01','2020-08-02','2020-08-03','2020-08-04');

…but if I pass this (''2020-08-01','2020-08-01','2020-08-02','2020-08-03','2020-08-04'') as a parameter it doesn't work.

I also tried to pass this ('2020-08-01,2020-08-01,2020-08-02,2020-08-03,2020-08-04')

… but it also doesn't work.

My idea is to put this string as a parameter through a procedure.
Perhaps like this:

IN parameter TEXT

BEGIN

SET @c = CONCAT('SELECT id_s FROM group_of_id_s
WHERE date IN (', '''' , parameter, '''', ')');

PREPARE stmt from @c;
EXECUTE stmt;

END

any reference or helpful advice would be appreciated.

EDIT: Based on very helpful example from @nbk and changing date format before comparing it to an array, I managed to put up a valid query that works like a charm. You can see it below in all of its glory. ;O) Response from @nbk is therefore considered as solution. Thanky you very much @nbk.

SELECT id_s FROM group_of_id_s
WHERE find_in_set(DATE_FORMAT(`date`, '%Y-%m-%d'), parameter) > 0;

Best Answer

Use find_in_Set

Schema (MySQL v5.7)

CREATE TABLE group_of_id_s (id_s int ,`date`date);

INSERT INTO group_of_id_s VALUES(1,'2020-08-01'),(2,'2020-07-01');


delimiter //
CREATE PROCEDURE myproc (IN parameter TEXT)
       BEGIN
         SELECT id_s FROM group_of_id_s
         WHERE find_in_set(`date`, parameter) > 0;
       END//
delimiter ;

Query #1

CALL myproc("2020-08-01,2020-08-01,2020-08-02,2020-08-03,2020-08-04");

| id_s |
| ---- |
| 1    |

View on DB Fiddle

Related Question