29

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,932
  • 11
  • 38
  • 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

2 Answers2

49

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
  • 37,556
  • 9
  • 94
  • 102
  • I totally forgot about FIND_IN_SET(), thanks for that comment above, too. – mr.b Aug 21 '12 at 23:20
  • 1
    Be mindful of spaces that you may have in your list. I.e. for a zip code example, '30305,30308' does not perform the same as '30305, 30308' – Justin Jul 14 '22 at 13:44
0

i was able to find a solution to this question, if you want to use NOT IN function in MySQL then you can try to use the following code. i hope this will be useful. it works like NOT IN except you pass the comma separated string

CREATE PROCEDURE `get_users_per_app` (id_list TEXT)
BEGIN
    SELECT
        app_id, GROUP_CONCAT(user_id)
    FROM
        app_users
    WHERE
       app_id NOT FIND_IN_SET(app_id, id_list)
    GROUP BY app_id;
Reg Miller
  • 1
  • 1