27

I'm writing a stored procedure which should pass its arguments to IN (..) part of query in the procedure body, like this:

DELIMITER //

CREATE PROCEDURE `get_users_per_app` (id_list TEXT)
BEGIN
    SELECT app_id, GROUP_CONCAT(user_id) FROM app_users WHERE app_id IN (id_list) GROUP BY app_id;
END//

DELIMITER ;

This, obviously, doesn't work because when I pass a textual value, id_list is interpolated as an integer and only first ID is considered and used inside of IN() condition.

I realize that this particular type of procedure could be instead replaced by the contained query, but I think that my question still stands - what if I needed to pass this kind of data?

I also realize that this approach of query might not be considered the best practice, but in my use case it's actually better than returning a flat list of ID-ID pairs..

mr.b
  • 4,861
  • 11
  • 36
  • 55
  • 1
    Check out the answer(s) to http://stackoverflow.com/questions/11957643/mysql-variable-format-for-a-not-in-list-of-values – newfurniturey Aug 21 '12 at 23:14

1 Answers1

46

You should be able to use MySQL's FIND_IN_SET() to use the list of ids:

CREATE PROCEDURE `get_users_per_app` (id_list TEXT)
BEGIN
    SELECT
        app_id, GROUP_CONCAT(user_id)
    FROM
        app_users
    WHERE
        FIND_IN_SET(app_id, id_list) > 0
    GROUP BY app_id;
    ...
newfurniturey
  • 35,828
  • 9
  • 90
  • 101