In MySQL there appears to not be support for passing a variable to a stored procedure and using that variable in an IN CLAUSE. Here’e the body of an extremely simple stored procedure that I will use to illustrate
the problem.

CREATE DEFINER=`root`@`localhost` PROCEDURE `spMySampleStoredProc`(idlist varchar(1000))

select * from tblSampleTable where theid in (idlist);

END$$
DELIMITER ;

If you run this with ‘123,456’ as the lone parameter then the result will annoyingly be the result as if you had just sent in ‘123’. That is, it only pays attention to the FIRST ITEM in the csl (comma seperated list) when
it is passed as a parameter.

So how can one work around this? Well, what I did was create a temporary table in the stored proc and parsed the parameter string, inserting each item in the string into the temporary table as I went. Then just just used an INNER JOIN (or an IN CLAUSE) against the temporary table. My code to create the temporary table is listed below:

declare seperatorcount int;
 declare idstring varchar(10);
 declare testifdonestring varchar(1000);

 CREATE TEMPORARY TABLE temptbl_IdList (pid int);

 set seperatorcount=1;
 myloop: LOOP
 set aplayeridstring = (
    SELECT trim(SUBSTRING_INDEX(SUBSTRING_INDEX(idlist, ‘,’, seperatorcount), ‘,’, -1))
    );
 
 insert into temptbl_IdList (pid) values (idstring);
  
 set testifdonestring = (
    SELECT trim(SUBSTRING_INDEX(SUBSTRING_INDEX(idlist, ‘,’, seperatorcount), ‘,’, -seperatorcount))
    );
 if idlist = testifdonestring
  then LEAVE myloop;
 end if;
 SET seperatorcount = seperatorcount + 1;
 
END LOOP myloop;

Now how whole proc would look something like this:

CREATE DEFINER=`root`@`localhost` PROCEDURE `spMySampleStoredProc`(idlist varchar(1000))

 declare seperatorcount int;
 declare idstring varchar(10);
 declare testifdonestring varchar(1000);

 CREATE TEMPORARY TABLE temptbl_IdList (pid int);

 set seperatorcount=1;
 myloop: LOOP
 set aplayeridstring = (
    SELECT trim(SUBSTRING_INDEX(SUBSTRING_INDEX(idlist, ‘,’, seperatorcount), ‘,’, -1))
    );
 
 insert into temptbl_IdList (pid) values (idstring);
  
 set testifdonestring = (
    SELECT trim(SUBSTRING_INDEX(SUBSTRING_INDEX(idlist, ‘,’, seperatorcount), ‘,’, -seperatorcount))
    );
 if idlist = testifdonestring
  then LEAVE myloop;
 end if;
 SET seperatorcount = seperatorcount + 1;
 
END LOOP myloop;

select * from tblSampleTable where theid in (select pid from temptbl_IdList);

drop table temptbl_IdList;

END$$
DELIMITER ;