title: design a stored procedure with multiple parameters and HAVING LIKE
body: <p>I' m trying to write a stored procedure that will search a fairly simple database with</p>
  
  <ul>
  <li>a USER table (user_id,name,...)</li>
  <li>a USER_TYPE table (user_id,type_id) - multi to multi</li>
  <li>a TYPE table (type_id,type_name)</li>
  <li>a USER_GAME (user_id,game_id) -multi to multi</li>
  <li>a GAME table (game_id,game_name)</li>
  </ul>
  
  <p>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 example 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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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</p>
  
  <pre><code>HAVING game LIKE CONCAT('%',NULL,'%').
  </code></pre>
  
  <p>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.</p>