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 ;
Why not just use a prepared statement inside the stored procedure? You can create the whole select query as a string inside a variable and simply concatenate in the comma delimited string into its IN clause. Then you can make a prepared statement from the query string variable and execute it.
DELIMITER ;;
create procedure testProc(in listString varchar(255))
BEGIN
set @query = concat('select * from testTable where id in (',listString,');');
prepare sql_query from @query;
execute sql_query;
END
;;
DELIMITER ;
call testProc("1,2,3");
Lee, unless you can absolutely control where this is being called from you are creating a big problem around input sanitisation.
Obligatory XKCD link
@Lee Fentress
Using prepared statement in a stored procedure is not allowed, you’ll get “Dynamic SQL is not allowed in stored function or trigger”.
Thanks a lot for sharing this, This “simple” example not only shows us how to solve passing a list of values as a parameter to a procedure in MySql, but it gives us much more knowledge in the process of doing so.
My situation required passing two parameters one INT and one VARCHAR (list), and since I’m new to MySQL this was a big (huge) deal for me. My problem was the INT value was also separated by a comma, so I needed to find the way how to “encapsulate” the VARCHAR list into one object value, and the INT into another simple value. And I found my answer in re-using some of the code contained in this tutorial: Before I send the list as a parameter I appended between curly brackkets: “{” + LIST + “}” and once inside the parameter I just remove the “{” and “}” all this by re-using some of the same code in this tutorial.
Thanks