SQL Where question

Question

Hi all,

I have a question about case statements and nulls in a where clause.

I want to do the following:

Declare @someVar int = null

select column1 as a from
TestTable t
where column1 = case when @someVar is not null then @someVar else column1 end

Here is the problem:

Let's say @someVar is null. Let's also say that column1 from TestTable t has NULL column values. Then, my condition t = t in the case statement will always evaluate to false.

I basically just want to be able to conditionally filter the column based on the value of @someVar if it's provided. Any help?

Answer 1

Maybe looking into COALESCE will help you.

Answer 2

• If @someVar IS NULL always return the row.
• If @someVar IS NOT NULL and column1 = @someVar return the row.
• Otherwise don't return the row.

So try this:

SELECT column1
FROM TestTable t
WHERE @someVar IS NULL OR column1 = @someVar

To test that this expression works try inserting some test values into a table and then fetch all the rows where b is NULL or a is equal to b:

CREATE TABLE Table1 (a VARCHAR(20) NULL, b VARCHAR(20) NULL);
INSERT Table1 (a, b) VALUES
(NULL, NULL),
('Foo', NULL),
(NULL, 'Foo'),
('Foo', 'Foo'),
('Bar', 'Foo');

SELECT *
FROM Table1
WHERE b IS NULL OR a = b

Result:

a     b
NULL  NULL
Foo   NULL
Foo   Foo

Answer 3

While conditional filtering is a mainstream pattern, I urge you to reconsider your intent. The more you compress multiple queries into a single shape, the more you interfere with the ability of the optimizer to figure out what your query does, and the more likely the resulting query execution plan will be poor.

In this case, the optimizer can't tell whether column1 is a filter or not without inspecting @somevar. So should an index on column1 be used or not?