How to Search Using Multiple Parameters While Including Nulls in Results in SQL-Server

Question

I'm trying to construct a simple query that will allow the user to filter a list of clients based on demographic information. It seems to me that LIKE would be the simplest solution, for instance:

SELECT D.Name
FROM demographics D

WHERE D.City LIKE @cityVar
AND D.Sex LIKE @sexVar
AND D.Age LIKE @ageVar
...

Variables are passed into the query using combo boxes; therefore if the user decides not to filter based on any given parameter, I simply pass '%' to the variable, effectively escaping that line in the WHERE clause.

However, this does not work if I would like to return rows containing NULL values in filtered fields. For instance, if D.Employer has a value of NULL, the client is unemployed; but passing '%' to @employedVar (meaning that the user would like to return both employed and unemployed clients) ignores all rows where D.Employer is NULL.

How would I construct the logic of the WHERE clause in order to account for a large number of parameters while still allowing the user to escape filtering on any given parameter, all the while including NULL values when appropriate?

cadrell0 · Accepted Answer · 2013-09-23 19:52:13Z

1

The simplest would be to do something like this

SELECT D.Name
FROM demographics D
WHERE (D.City LIKE @cityVar OR @cityVar == '%')
    AND (D.Sex LIKE @sexVar OR @sexVar == '%')
    AND (D.Age LIKE @ageVar OR @ageVar == '%')

It is important that you do not use something like

SELECT D.Name
FROM demographics D
WHERE ISNULL(D.City. '') LIKE @cityVar
    AND ISNULL(D.Sex, '') LIKE @sexVar
    AND ISNULL(D.Age, '') LIKE @ageVar

Passing the values into a function will made it so SQL Server is unable to use any indexes that you may have on these columns. This will result in inefficient query plans and poor performance.

answered Sep 23, 2013 at 19:52

cadrell0

17.4k5 gold badges55 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cadrell0 Over a year ago

In the first example, swapping the order of the ORs may improve performance (due to short circuiting), but I'm unable to test it right now.

Mike Perrenoud · Accepted Answer · 2013-09-23 19:49:06Z

1

This might work for you. How about interpreting null in the procedure to indicate that the user wants everything except null. In other words, if the user didn't specify anything, let's give them back everything with a value. So something like this:

SELECT ...
FROM ...
WHERE ((@parm1 IS NULL AND field1 IS NOT NULL) OR
       (field1 LIKE '%' + @parm1 + '%'))

This query will return all rows that have a value if the user didn't specify anything, but if they did it will run the LIKE.

This just means that from the application, if the user doesn't specify anything (or maybe in your case specifies all) you send null to the procedure.

answered Sep 23, 2013 at 19:49

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

Collectives™ on Stack Overflow

How to Search Using Multiple Parameters While Including Nulls in Results in SQL-Server

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related