MessorG MessorG - 1 year ago 87
SQL Question

SQL - Operator LIKE returns '%' or null

I needs to create a query to the database that will filter the results like this:

[ProjectNumber] LIKE
CASE WHEN IsNull(@ProjectId,'') = '' THEN
'%'
ELSE
@ProjectId + '%'
END
AND
[CountryCode] LIKE
CASE WHEN IsNull(@Country,'') = '' THEN
'%'
ELSE
@Country + '%'
END


When both variables have value everything works, but if @Country is null then this query returns all results for which in the CountryCode column there is a value, and I need to return all (even if the field is Null). Anyone know how to write this query in case of null variable that returned all the fields (fields with value and fields with null)?

Answer Source

Instead of using CASE ... WHEN ... and ISNULL function, you can do this:

...
(@ProjectId IS NULL OR [ProjectNumber] LIKE @ProjectId + '%')
AND
(@Country IS NULL OR [CountryCode] LIKE @Country + '%')
...
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download