Using MySQL user-defined variable in stored procedure for 'in' clause
mysql, sql, stored-procedures
Solution
due to the way parameterization works, this is not possible.
the closest you can get it this:
where find_in_set(id, ?)
but this will not scale as as can not use an index.
Problem
When sending a string of comma separated ids, as a varchar, to a MySQL stored procedure I can not use the string as part of an IN clause with the correct results returned. The string is truncated as a decimal and only the first value is being used. I thought I would be able to get around this by preparing and then executing the statement, but this still only returned a match for the first value. Code examples may make things a bit clearer. I want to convert the following into a stored procedure (with the in clause dynamic): ``` select id, name from cities where id in (1,2,3); ``` This is my stored procedure using a prepared statement: ``` DROP PROCEDURE IF EXISTS `cities_select_by_ids` $$ CREATE PROCEDURE `cities_select_by_ids`( in _cityIds varchar(1000) ) BEGIN SET @cityIds = _cityIds; PREPARE stmt FROM ' select id, name from cities where id in (?); '; EXECUTE stmt USING @cityIds; DEALLOCATE PREPARE stmt; END $$ DELIMITER ; ``` Calling the stored procedure I only get a match for the city '1': ``` call cities_select_by_ids_prepare('1, 2, 3'); ``` Here is a create and insert script for the table and data: ``` CREATE TABLE cities ( id int(10) unsigned NOT NULL auto_increment, name varchar(100) NOT NULL, PRIMARY KEY (`id`) ); insert into cities (name) values ('London'), ('Manchester'), ('Bristol'), ('Birmingham'), ('Brighton'); ```