title: MySQL - NULL value check and Dynamic SQL inside stored procedure
body: <pre><code>DROP PROCEDURE IF EXISTS HaveSomeFun;
  CREATE PROCEDURE HaveSomeFun(user_id CHAR(50),house_id CHAR(50),room_id CHAR(50),fun_text TEXT,video_url CHAR(100))
  BEGIN
   DECLARE query_full TEXT;
   SET @fields_part = 'INSERT INTO fun(FunKey,UserKey,FunBody,LastModified';
   SET @values_part = CONCAT(') VALUES( NewBinKey(), KeyToBin(\"', user_id, '\"), \"', fun_text, '\", NOW() ');
   IF (house_id) THEN
    SET @fields_part = CONCAT(@fields_part, ', HouseKey');
    SET @values_part = CONCAT(@values_part, ', KeyToBin(\'', house_id, '\')');
   END IF;
   IF (room_id) THEN
    SET @fields_part = CONCAT(@fields_part, ', RoomKey');
    SET @values_part = CONCAT(@values_part, ', KeyToBin(\'', room_id, '\')');
   END IF;
   IF (video_url IS NOT NULL) THEN
    SET @fields_part = CONCAT(@fields_part, ', VideoURL');
    SET @values_part = CONCAT(@values_part, ', "', video_url, '"');
   END IF;
   SET query_full  = CONCAT(@fields_part, @values_part, ' );');
   SET @query_full = query_full;
   PREPARE STMT FROM @query_full;
   EXECUTE STMT;
   SELECT query_full;
  END;
  </code></pre>
  
  <p>And</p>
  
  <pre><code>CALL HaveSomeFun('29B455DE-A9BC-102D-9C16-00163EEDFCFC', '', 'F82C47A8-64DE-11DF-9D7E-0026B9481364', 'Jokes apart', '');
  </code></pre>
  
  <p>will construct the below string in the variable query_full</p>
  
  <pre><code>INSERT INTO fun(FunKey,UserKey,FunBody,LastModified, VideoURL) VALUES( NewBinKey(), KeyToBin("29B455DE-A9BC-102D-9C16-00163EEDFCFC"), "Jokes apart", NOW() , "" );
  </code></pre>
  
  <p>But I need to get</p>
  
  <pre><code>INSERT INTO fun(FunKey,UserKey,FunBody,LastModified, RoomKey, VideoURL) VALUES( NewBinKey(), KeyToBin("29B455DE-A9BC-102D-9C16-00163EEDFCFC"), "Jokes apart", NOW() , KeyToBin('F82C47A8-64DE-11DF-9D7E-0026B9481364'), "" );
  </code></pre>
  
  <p>Something is missing in the check <code>IF (room_id) THEN</code>. But I cannot impose <code>IF (room_id IS NOT NULL) THEN</code> since it will create KeyToBin('') and RoomKey is foreign key , KeyToBin('') will produce an invalid RoomKey.</p>
  
  <p>Any Idea?</p>