• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

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);
0
slattbr
Asked:
slattbr
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now