title: mysql how to pass null parameter into stored procedure and test for it in sql statement
body: <p><strong>NOTE: Solution for this issue has been attached at the bottom. :)</strong></p>
  
  <p>5.6.17 - MySQL Community Server (GPL) Using MySQL Console</p>
  
  <p>Trying to Test this procedure out in mysql console. It actually involves numerous fields which maybe searched against. Some values maybe defined as NULL. </p>
  
  <p>I was having troubles with the query with an ERROR #1064 which involved a NULL value at line 1.</p>
  
  <p>Here is the query and it breaks when I added the <code>@P1 IS NULL</code> test. I saw this somewhere but cannot for the life of my find it again... </p>
  
  <pre><code>SET @p0='46,51,52,1317,1318,1319,1320,1322,1323'; 
  SET @p1='500-000'; 
  CALL `searchCount2`(@p0, @p1);
  
  DROP PROCEDURE IF EXISTS `searchCount2`//
  
  CREATE PROCEDURE `searchCount2`(
  IN _dealerIds varchar(100),
  IN _dealerPhoneNumber varchar(10)
  )
  BEGIN
          SET @query = CONCAT('SELECT count(cID) 
          FROM tblclassifieds c
          JOIN tblphotos p 
          ON c.cmd5val=p.cClassCode 
          WHERE p.cpMain=1 
          AND c.cMarkedInappropriate=0 
          AND c.cBlacklisted=0 
          AND c.cEndDate&gt;NOW() 
          AND (cType=29) OR (c.cType=27 OR c.cType=28) 
          AND c.cCompanyId IN (',_dealerIds,')
          AND (("',_dealerPhoneNumber,'" is null) or (c.cPhoneNum="',_dealerPhoneNumber,'"));');
      --  SELECT @query;
       PREPARE stmt FROM @query;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
  END //
  </code></pre>
  
  <p>Retested the Above using quotes. </p>
  
  <p>Here is the example I have which works before I added the <code>@P1 IS NULL</code>, but as I mentioned this query is far from complete. There are numerous parameters to search against.</p>
  
  <pre><code>DROP PROCEDURE IF EXISTS `searchCount3`//
  
  CREATE PROCEDURE `searchCount3`(
  IN _dealerIds varchar(100),
  IN _dealerPhoneNumber varchar(10)
  )
  BEGIN
          SET @query = CONCAT('SELECT count(cID) 
          FROM tblclassifieds c
          JOIN tblphotos p 
          ON c.cmd5val=p.cClassCode 
          WHERE p.cpMain=1 
          AND c.cMarkedInappropriate=0 
          AND c.cBlacklisted=0 
          AND c.cEndDate&gt;NOW() 
          AND ((cType=29) OR (cType=27 OR cType=28))
          AND c.cCompanyId IN (',_dealerIds,')
          OR c.cPhoneNum=',_dealerPhoneNumber,';');
       -- SELECT @query;
      PREPARE stmt FROM @query;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
  END //
  </code></pre>
  
  <p>SO what is my error with this NULL error? Is there another way I should implement this? How can I test it in MySQL?</p>
  
  <p>Is Set @p1=NULL; valid?</p>
  
  <p>Please disregard the horrible naming convention.</p>
  
  <p>Thanks for any help. I have been struggling with this for too long.</p>
  
  <p>Here is a print off of the query before it executes, <code>SELECT @query</code>:</p>
  
  <pre><code>SELECT count(cID)
                FROM tblclassifieds c
                JOIN tblphotos p
                ON c.cmd5val=p.cClassCode
                WHERE p.cpMain=1
                AND c.cMarkedInappropriate=0
                AND c.cBlacklisted=0
                AND c.cEndDate&gt;NOW()
                AND (cType=29)
                AND c.cCompanyId IN (46,51,52,1317,1318,1319,1320,1322,1323)
                OR (cType=27 OR cType=28)
                AND cCompanyId IN (46,51,52,1317,1318,1319,1320,1322,1323)
                AND ((579-7775 is null) or (c.cPhoneNum=579-7775)); 
  </code></pre>
  
  <p>I copy and paste this query into sql console and I get results. But Execute fails! Why is this so? Error #1064.</p>
  
  <p><strong>SOLUTION:</strong></p>
  
  <p>I removed the parameter test for _dealerPhoneNumber IS NULL and replaced it with _dealerPhoneNumber = "". This has fixed the issue.</p>
  
  <pre><code>DROP PROCEDURE IF EXISTS `searchCount2`//
  
  CREATE PROCEDURE `searchCount2`(
  IN _dealerIds varchar(100),
  IN _dealerPhoneNumber varchar(10)
  )
  BEGIN
          SET @query = CONCAT('SELECT count(cID) 
          FROM tblclassifieds c
          JOIN tblphotos p 
          ON c.cmd5val=p.cClassCode 
          WHERE p.cpMain=1 
          AND c.cMarkedInappropriate=0 
          AND c.cBlacklisted=0 
          AND c.cEndDate&gt;NOW() 
          AND ((cType=29) AND cCompanyId IN (',_dealerIds,')) 
          OR ((cType=27 OR cType=28) AND cCompanyId IN (',_dealerIds,')) 
          AND (("',_dealerPhoneNumber,'" = "") or (c.cPhoneNum="',_dealerPhoneNumber,'"));');
      --  SELECT @query;
       PREPARE stmt FROM @query;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
  END //
  </code></pre>