SQL Server where case only if not null

Question

I have to select rows following some variables which will switch on or off some conditions, like:

SELECT *
FROM table
WHERE field =
   CASE @param
       WHEN NULL THEN field
       ELSE @param
   END

In another words.. I want to compare only if @param is not null, but my select doesn't work. How can I do it?

Thanks!

Your code should work except when field has a NULL value. — Gordon Linoff
– Gordon Linoff, Commented Nov 19, 2015 at 14:32

Doolius · Accepted Answer · 2015-11-19 13:54:00Z

2

When @param is null it will use field, when @param is not null it will use @param:

SELECT *
FROM table
WHERE field = ISNULL(@param,field)

But field = field will always be true. So what you might want is:

SELECT *
FROM table
WHERE field = @param
    and @param is not null

answered Nov 19, 2015 at 13:54

Doolius

8946 silver badges18 bronze badges

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

Comments

Brad D · Accepted Answer · 2015-11-19 13:55:50Z

2

Why use a Case for a single switch? an OR statement should do the trick.

SELECT *
FROM table
WHERE @param IS NULL OR Field = @Param

answered Nov 19, 2015 at 13:55

Brad D

7624 silver badges8 bronze badges

Slasko · Accepted Answer · 2015-11-19 13:52:41Z

0

Should be like this:

SELECT *
FROM table
WHERE field =
   CASE 
   WHEN @param IS NULL THEN field
   ELSE @param
END

answered Nov 19, 2015 at 13:52

Slasko

4172 silver badges8 bronze badges