XUq5gMBQ

1select id, name from cities where id in (1,2,3);
2	
3DROP PROCEDURE IF EXISTS `cities_select_by_ids` $$
4CREATE PROCEDURE `cities_select_by_ids`(
5    in _cityIds varchar(1000)
6)
7BEGIN
8SET  @cityIds = _cityIds;
9
10PREPARE stmt FROM '
11    select
12      id,
13      name
14    from cities
15    where id in (?);
16';
17
18EXECUTE stmt USING @cityIds;
19DEALLOCATE PREPARE stmt;
20
21END $$
22DELIMITER ;
23	
24call cities_select_by_ids_prepare('1, 2, 3');
25	
26CREATE TABLE cities (
27  id int(10) unsigned NOT NULL auto_increment,
28  name varchar(100) NOT NULL,
29  PRIMARY KEY  (`id`)
30);
31insert into cities (name) values ('London'), ('Manchester'), ('Bristol'), ('Birmingham'), ('Brighton');
32	
33where find_in_set(id, ?)
34	
35DROP PROCEDURE IF EXISTS `cities_select_by_ids_2`;
36
37CREATE PROCEDURE `cities_select_by_ids_2`(
38    in cityIDs varchar(1000)
39)
40
41BEGIN
42
43#- ix - index into the list of city IDs
44# cid - city ID
45SET @ix := 1;
46SET @cid := substring_index(cityIDs, ',', @ix);
47
48LOOP_1: 
49WHILE (@cid is not null) DO
50    SELECT id,  name 
51    FROM cities
52        WHERE id in (@cid) ;
53
54     #-- substring_index returns complete cityIDs string when index is > number of elements
55     IF (length(substring_index(cityIDs, ',', @ix)) >= length(cityIDs)) THEN
56          LEAVE LOOP_1;
57     END IF;
58
59     SET @ix := @ix + 1;
60     SET @cid = substring_index(substring_index(cityIDs, ',', @ix), ',', -1);
61
62END WHILE;
63END 
64
65#----
66call cities_select_by_ids_2('1, 2');