CODEWITHSUNDEEP
mysqlsearchstored-proceduresmultiple-columns
Joe
Joe

Reputation: 2591

MySQL Stored Pocedures skip WHERE condition if parameter is null

I'm writing stored procedure that should search for records base on few procedure arguments. The problem is that not always all arguments have to be passed, sometimes they may be set to NULL.

Is there a way to write sth that would work like that?

CREATE PROCEDURE testProc(IN p_idWorker INTEGER, IN p_idEffect INTEGER)
BEGIN
    SELECT 
        * 
    FROM 
        CallHistory
    WHERE
        idWorker = IFNULL(p_idWorker, ANYTHING)
        AND 
        idEffect = IFNULL(p_idEffect, ANYTHING);
END$$

Upvotes: 2

Views: 2688

Answers (3)

Joe
Joe

Reputation: 2591

First of all thank you Mahmoud and valex for your time but both answers are not totally good. They will not work if for example the field idWorker is nullable - it will not see the rows where field idWorker IS NULL.

The ultimate solution to this looks weird but it works:

...
WHERE 
    idWorker = IFNULL(p_idWorker, idWorker)
    AND
    IFNULL(ch.idProjTime, -1) = IFNULL(p_idProjTime, IFNULL(ch.idProjTime, -1))

Now it'll see NULL fields too.

If it is bad idea to sth like that (I can see probable performance impact - 3 times a row it does the IFNULL condition) - please correct me.

Upvotes: 2

valex
valex

Reputation: 24144

You can use for example: 

    idWorker = IFNULL(p_idWorker, idWorker)

IF p_idWorker is null then this condition is always TRUE for all rows. If not then it is true only if idWorker = p_idWorker

Upvotes: 3

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79939

Like so:

 ...
 WHERE (p_idWorker IS NULL OR idWorkder = p_idWorker)
   AND (p_idEffect IS NULL OR idEffect = p_idEffect);

Or, like the way you did, but instead of Anything use the column name instead like so:

   ...
   WHERE
        idWorker = IFNULL(p_idWorker, idWorker )
        AND 
        idEffect = IFNULL(p_idEffect, idEffect );

Upvotes: 4

Related Questions