How to search a string within a dynamic list of string in MYSQL Stored Procedure

Question

In my database I'm searching for a list of ID's like this:

SELECT * from testcase where id in ('658', '659');

But how can I make this into a stored procedure, which can take multiple id's?

In this example I have only '658', '659' but this could be more/less.

I made a stored procedure, but either it failes because I have two id's or more, and if I make it into a single string like this, '658, 659', then it only find the results from 658:

CREATE DEFINER=`edmetrics`@`%` PROCEDURE `GetCaseFromStrList`(CaseIDs varchar(300))
BEGIN
IF CaseIDs != 0 OR CaseIDs IS NOT NULL THEN
        Select testcase.id, testcase.TestCaseName
      From testcase
      Where testcase.id in (CaseIDs);
    END IF;
END

Is there a way to make a call like this, with multiple X amount of id's?

CALL database.GetCaseFromStrList(658 , 659);

EDIT - more info

Was told I cold look at this solution, but it's not 100% the thing im looking for: StackOverflow Link

But I will show an example with pictures

When I write this SQL: SELECT * from testcase where id in ('658', '659'); I get the following result

Table result

If I try to implement the linked solution with FIND_IN_SET() in my stored procedure I have this:

CREATE DEFINER=`edmetrics`@`%` PROCEDURE `GetCaseFromStrList`(CaseIDs varchar(300))
BEGIN
SET @idCaseIDs = CaseIDs;
IF CaseIDs != 0 OR CaseIDs IS NOT NULL THEN
        Select testcase.id, testcase.TestCaseName
      From testcase
      WHERE FIND_IN_SET(testcase.id, @idCaseIDs);
    END IF;
END

But if I use a string with all the ID's in it, and make a call like this: CALL testreporting.GetCaseFromStrList('658, 659');

I get the following result

Table result from stored procedure

And in that result I'm missing the ID 659.

and remember this is with two ID's, but I can have X amount of ID's not only two.

Does this answer your question? [MySQL variable format for a "NOT IN" list of values](https://stackoverflow.com/questions/11957643/mysql-variable-format-for-a-not-in-list-of-values) — Andrew Morton, Apr 04 '21 at 16:49
@AndrewMorton Not really, I will post pictures to make a better understanding of my problem — The Mungax, Apr 04 '21 at 16:56
try: **WHERE FIND_IN_SET(testcase.id, CaseIDs )** or see this https://stackoverflow.com/questions/8149545/pass-array-to-mysql-stored-routine — Bernd Buffen, Apr 04 '21 at 17:01

score 0 · Answer 1 · answered Apr 04 '21 at 17:12

Find_IN_SET is returning the position of the first string in the array of strings. So when you are string value is 658 it's returning 1 so WHERE FIND_IN_SET(testcase.id, @idCaseIDs) is true since in MySQL 1 represents that. But when the string value is 659 it's returning 2 which is not equivalent of true.

Instead please use condition FIND_IN_SET(testcase.id, @idCaseIDs)>0 to get your desired results.

CREATE DEFINER=`edmetrics`@`%` PROCEDURE `GetCaseFromStrList`(CaseIDs varchar(300))
BEGIN
SET @idCaseIDs = CaseIDs;
IF CaseIDs != 0 OR CaseIDs IS NOT NULL THEN
        Select testcase.id, testcase.TestCaseName
      From testcase
      WHERE FIND_IN_SET(testcase.id, @idCaseIDs)>0;
    END IF;
END

score 0 · Accepted Answer · answered Apr 04 '21 at 20:32

fIND_IN_SET works just fine, see example

CREATE TABLE testcase(id int,TestCaseName varchar(50) )

INSERT INTO testcase VALUES (658,'test1'),(659,'test2')

CREATE  PROCEDURE `GetCaseFromStrList`(_CaseIDs varchar(300))
BEGIN

IF _CaseIDs != 0 OR _CaseIDs IS NOT NULL THEN
        Select testcase.id, testcase.TestCaseName
      From testcase
      WHERE FIND_IN_SET(testcase.id, _CaseIDs);
    END IF;
END

CALL GetCaseFromStrList('658,659')

 id | TestCaseName
--: | :-----------
658 | test1       
659 | test2       

✓

db<>fiddle here

How to search a string within a dynamic list of string in MYSQL Stored Procedure

2 Answers2