design a stored procedure with multiple parameters and HAVING LIKE

I' m trying to write a stored procedure that will search a fairly simple database with

• a USER table (user_id,name,...)
• a USER_TYPE table (user_id,type_id) - multi to multi
• a TYPE table (type_id,type_name)
• a USER_GAME (user_id,game_id) -multi to multi
• a GAME table (game_id,game_name)

A same user can have several games. Now, I want to be able to get the user according to a particular type and also according to a/some particular game(s), so that for exam开发者_JAVA百科ple I can get all the user with, say type1, and with the games, say game2 and game5. I think I can get round the problem of several game names by passing them as a string parameter and do some kind of HAVING LIKE condition (I call get_user_spec('type1' , 'game3,game5') for example). So far I get to that point:

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_user_spec`(
                      IN inTypeName VARCHAR(50),
                      IN inGameName VARCHAR(150)
)

BEGIN 

    PREPARE statement FROM

    "SELECT u.user_id,t.type_name,GROUP_CONCAT(g.game_name) AS game
     FROM user u
     INNER JOIN user_type ut
         ON u.user_id=ut.user_id
     INNER JOIN type t
         ON ut.type_id=t.type_id
     LEFT JOIN user_game ug
         ON u.user_id=ug.user_id
     LEFT JOIN game g
         ON ug.game_id=g.game_id
     WHERE t.type_name=?
     GROUP BY u.user_id
     HAVING game LIKE CONCAT('%',?,'%')
     ORDER BY u.user_id";

    SET @p1=inTypeName;
    SET @p2=inGameName;

    EXECUTE statement USING @p1,@p2;

END

But my real problem is that if I don't pass any game name, I then want to get all users with type1 (I then call get_user_spec('type1' , NULL). But I am then not getting anything as the procedure sees

HAVING game LIKE CONCAT('%',NULL,'%').

I hope that was clear enough. If anybody has any suggestions to get around that problem, I would be very grateful. Thank you very much.

Change this line:

EXECUTE statement USING @p1,@p2;

to

EXECUTE statement USING @p1, ifnull(@p2, '');

This will cause the LIKE expression to be just '%%', which means "match everything"