slattbr
asked on
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);
HAVING (VW_HOURS.PROJECTID LIKE '%' + @PROJECTCONTAINS + '%') or (VW_HOURS.PROJECTID = @PROJECTID);
Aneesh
WHERE (@PROJECTCONTAINS IS NULL OR VW_HOURS.PROJECTID LIKE '%' + @PROJECTCONTAINS + '%') or (VW_HOURS.PROJECTID = @PROJECTID);
>>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);
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);
On second thoughts, that is not going to cut it as if one of the parameters is Null, both will get ignored.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The solution provided ignores the @projectid if null, but does not ignore the @projectcontains when it is null.
ASKER
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));
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));
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);
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);
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.