SQL query which will ignore null parameters

I have two parameters in my query.  I need to ignore the parameter if the parameter is null.  Below is what I have below.  What am I missing.  Thanks.

HAVING (VW_HOURS.PROJECTID LIKE '%' + @PROJECTCONTAINS + '%') or  (VW_HOURS.PROJECTID = @PROJECTID);
slattbrAsked:
Who is Participating?
 
Anthony PerkinsCommented:
So perhaps something like this may be more appropriate:
HAVING (VW_HOURS.PROJECTID LIKE '%' + @PROJECTCONTAINS + '%' or (@PROJECTCONTAINS IS NULL And @PROJECTID IS NOT NULL)) or  (VW_HOURS.PROJECTID = @PROJECTID Or (@PROJECTID IS NULL And @PROJECTCONTAINS IS NOT NULL));
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
WHERE  (@PROJECTCONTAINS  IS NULL OR VW_HOURS.PROJECTID LIKE '%' + @PROJECTCONTAINS + '%') or  (VW_HOURS.PROJECTID = @PROJECTID);
0
 
Anthony PerkinsCommented:
>>HAVING (VW_HOURS.PROJECTID LIKE '%' + @PROJECTCONTAINS + '%') or  (VW_HOURS.PROJECTID = @PROJECTID); <<
That would seem that it should be in a WHERE clause and not a HAVING, but in any case:
HAVING (VW_HOURS.PROJECTID LIKE '%' + @PROJECTCONTAINS + '%' or @PROJECTCONTAINS IS NULL) or  (VW_HOURS.PROJECTID = @PROJECTID Or @PROJECTID IS NULL);
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Anthony PerkinsCommented:
On second thoughts, that is not going to cut it as if one of the parameters is Null, both will get ignored.
0
 
slattbrAuthor Commented:
The solution provided ignores the @projectid if null, but does not ignore the @projectcontains when it is null.
0
 
slattbrAuthor Commented:
The following should works independently  

1)   Having (VW_HOURS.PROJECTID = @PROJECTID AND (@PROJECTID IS NOT NULL))
2)   Having (VW_HOURS.PROJECTID LIKE  @PROJECTCONTAINS + '%' AND (@PROJECTCONTAINS IS NOT NULL))

However, run into the same problem of ignoring the @projectid if null when I combine the two.

HAVING  (VW_HOURS.PROJECTID = @PROJECTID AND (@PROJECTID IS NOT NULL)) OR  (VW_HOURS.PROJECTID LIKE  @PROJECTCONTAINS + '%' AND (@PROJECTCONTAINS IS NOT NULL));
0
 
Kevin CrossChief Technology OfficerCommented:
Order matters.  See Anesh's suggestion - http:#a26192993 - as the IS NULL check needs to occur first in both cases.

Here is how it would look with both.

WHERE  (@PROJECTCONTAINS  IS NULL OR VW_HOURS.PROJECTID LIKE '%' + @PROJECTCONTAINS + '%')
OR (@PROJECTID IS NULL OR VW_HOURS.PROJECTID = @PROJECTID);
0
 
slattbrAuthor Commented:
MWVISA1 - Your solution resulted in the same problem of ignoring the @projectid.  The only way that I can make it work is with the following:

HAVING  (VW_HOURS.PROJECTID = @PROJECTID AND (@PROJECTID IS NOT NULL)) OR ((VW_HOURS.PROJECTID) LIKE  '%' + @PROJECTCONTAINS + '%' AND (@PROJECTID<1))

However, do not know why it works when looking for a value less than 1.  
0
 
Kevin CrossChief Technology OfficerCommented:
But I thought you wanted the @PROJECTID parameter ignored if it were NULL?
If the parameter is NULL and ignored, I would suspect that to be show all records... maybe expectations are different.  Glad you found a way that works though.

And note I was only addressing how to ignore the parameter within each logical block - in terms of the IS NULL piece being listed first.  Now looking at your full statement the OR in the middle of the two says one or the other is true so if the first parameter is NULL then the second would indeed be ignored even if it is NOT NULL.  Changing to check on the @PROJECTID fixes that if that is typically NOT NULL but this may also work.

WHERE  (@PROJECTCONTAINS  IS NULL OR VW_HOURS.PROJECTID LIKE '%' + @PROJECTCONTAINS + '%')
AND (@PROJECTID IS NULL OR VW_HOURS.PROJECTID = @PROJECTID);
0
 
slattbrAuthor Commented:
I do want the @projectid ignored if it is null, but the other solutions did not work. Maybe it is something in the way I have the parameters set up.  A problem for another day.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.