mysql how to pass null parameter into stored procedure and test for it in sql statement

Question

NOTE: Solution for this issue has been attached at the bottom. :)

5.6.17 - MySQL Community Server (GPL) Using MySQL Console

Trying to Test this procedure out in mysql console. It actually involves numerous fields which maybe searched against. Some values maybe defined as NULL.

I was having troubles with the query with an ERROR #1064 which involved a NULL value at line 1.

Here is the query and it breaks when I added the @P1 IS NULL test. I saw this somewhere but cannot for the life of my find it again...

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>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 //

Retested the Above using quotes.

Here is the example I have which works before I added the @P1 IS NULL, but as I mentioned this query is far from complete. There are numerous parameters to search against.

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>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 //

SO what is my error with this NULL error? Is there another way I should implement this? How can I test it in MySQL?

Is Set @p1=NULL; valid?

Please disregard the horrible naming convention.

Thanks for any help. I have been struggling with this for too long.

Here is a print off of the query before it executes, SELECT @query:

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>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)); 

I copy and paste this query into sql console and I get results. But Execute fails! Why is this so? Error #1064.

SOLUTION:

I removed the parameter test for _dealerPhoneNumber IS NULL and replaced it with _dealerPhoneNumber = "". This has fixed the issue.

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>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 //

Answer 1

I had a similar issue. I was passing NULL to a MySQL stored procedure and getting this error message that I thought was obscure:

ERROR #1064 which involved a 'NULL' value at line 1.

I thought maybe passing NULL to stored procedures was not allowed, but it is allowed.

My issue was in the stored procedure, specifically:

• I was dynamically generating an insert statement via a call to CONCAT, AND
• i was not guarding against the presence of NULL, so i ended up with a quoted 'NULL' in my insert statement

By adding a condition in my string concatenation to account for NULL i was able to fix my issue

BEFORE

...
"more_info_link = '", more_info_link, "', ",
...

AFTER

IF(more_info_link IS NULL,
  "more_info_link = NULL, ",
  CONCAT("more_info_link = '", more_info_link, "', ")
),

Answer 2

try to define the parameters as null on the creation.

CREATE PROCEDURE `searchCount3`(
IN _dealerIds varchar(100) = NULL,
IN _dealerPhoneNumber varchar(10) = NULL
)