MySQL SP parameter for IN clause

Recently, I came across a question on stackoverflow.com where the poster indicated that he was not able to use the parameter string as part of stored procedure IN clause to get the right results and the result set always returned one row only.

Test table used for procedure:

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');

Original stored procedure:

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 ;

call cities_select_by_ids_prepare('1, 2, 3');
#-- Only 1 city name returned (London).

One work around would be to split the string into individual elements and run the select as below.

DROP PROCEDURE IF EXISTS `cities_select_by_ids_2` ;
CREATE PROCEDURE `cities_select_by_ids_2`(
in cityIDs varchar(1000)
)
BEGIN

#- ix - index into the list of city IDs
#- cid - city ID
SET @ix := 1;
SET @cid := substring_index(cityIDs, ',', @ix);

LOOP_1:
WHILE (@cid is not null) DO
SELECT id, name
FROM cities
WHERE id in (@cid) ;

#-- substring_index returns complete cityIDs string when index is > number of elements
IF (length(substring_index(cityIDs, ',', @ix)) >= length(cityIDs)) THEN
LEAVE LOOP_1;
END IF;

SET @ix := @ix + 1;
SET @cid = substring_index(substring_index(cityIDs, ',', @ix), ',', -1);

END WHILE;
END

#----
call cities_select_by_ids_2('1, 2, 3');

4 thoughts on “MySQL SP parameter for IN clause

  1. /* Only Works in MySQL 5.0 and above */

    BEGIN
    SET @sql_text:=concat(
    'SELECT
    id,
    name
    FROM db.table
    WHERE
    ProjectId IN
    (',

    /* BEGIN Parameter */
    '1,2,3',
    /* END Parameter */

    ' ORDER BY id ASC');

    PREPARE stmt from @sql_text;
    EXECUTE stmt ;
    DEALLOCATE PREPARE stmt;
    END

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s