Best way to do a CASE where clause SQL Server

Question

I am trying to build an SQL SP to do a query for report. It has several arguments that could be NULL or should have a value. I am using the code below. Is there another alternative way of doing this, more cleanly or more proper. I am assuming that if an Argument is Null, i would just do a 1=1 filter condition.

DECLARE @Arg1 VARCHAR(10) = NULL,
        @Arg2  VARCHAR(10) = NULL

SELECT * FROM Table1
WHERE 
(CASE WHEN (@Arg1 IS NULL) THEN
        1
    ELSE
        @Arg1 
    END) = 
    (CASE WHEN (@Arg1 IS NULL) THEN
        1
    ELSE
        Location
    END)
AND
    (CASE WHEN (@Arg2 IS NULL) THEN
        1
    ELSE
        @Arg2 
    END) = 
    (CASE WHEN (@Arg2 IS NULL) THEN
        1
    ELSE
        Sex
    END)

Take a look at this article. What you are doing is a type of catch all query. There can be some serious performance issues if not handled correctly. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ — Sean Lange, Aug 20 '14 at 21:26
Hi @SeanLange, i did not quite read the article properly, any tips on what should I use to handle this correctly. — Sid, Aug 20 '14 at 21:53

score 4 · Answer 1 · answered Aug 20 '14 at 21:04

4

Can't you just do it like this?

where 
(@Arg1 is null or @Arg1 = Location)
and
(@Arg2 is null or @Arg2 = Sex)

Then if @Arg1 (or 2) is null then that part of the predicate is TRUE and and'd with the next predicate.

answered Aug 20 '14 at 21:04

DaveShaw

52,123
16
112
141

there we go, just went with this code i saw on other SP, this one seems better. Is this the best on my case? – Sid Aug 20 '14 at 21:05
I don't know much about your case, but this is easier to read and more commonly used. I use this approach every time I've had this problem, and I have never needed an alternative. – DaveShaw Aug 20 '14 at 21:06
Thanks, going with this – Sid Aug 20 '14 at 21:08

score 4 · Answer 2 · answered Aug 20 '14 at 21:16

4

Another possibility, even shorter, is to use Coalesce():

where COALESCE(@Arg1, Location) = Location
  and COALESCE(@Arg2, Sex) = Sex

That is, when @arg1 and @arg2 have values, use them, otherwise, otherwise use the column in question; since Location = Location is always true (at least when not NULL, same issue in your own code).

answered Aug 20 '14 at 21:16

SingleNegationElimination

151,563
33
264
304

1

This was my go ahead plan but Location = Location is not a factor of perf issues? – Sid Aug 20 '14 at 21:50
@Sid I've just made a test, and looks like `Location = Location` is evaluated in a way different from what you thought. It's in fact very fast. The reference of column to itself can be derived by the SQL Server and the expression of `true` is return nearly immediately. The string comparison happens only when there is some argument of character-based type in the expresion. – King King Aug 20 '14 at 22:03
@KingKing Should we say Location = Location is infact faster/the same comparing to NULL IS NULL ? – Sid Aug 20 '14 at 22:05
@Sid I think they are almost the same. – King King Aug 20 '14 at 22:14
There is a serious difference, if the column on the right side is nullable, because `NULL = NULL` is not a match; In that case, something like `COALESCE(@arg1, location, 1) = COALESCE(location, 1)` might be better, but in that case, there's not much readability improvement here, so i'd go with Dave's solution. when it's *not* nullable, then the planner can (in principle, anyways) avoid examining the column at all. – SingleNegationElimination Aug 21 '14 at 01:36

score 1 · Accepted Answer · edited May 23 '17 at 11:49

Another possibility is

where IsNull(@Arg1,Location) = Location
  and IsNull(@Arg2, Sex) = Sex

This is pretty much the @IFLoop answer, but IsNull is slightly faster than COALESCE.

Which is quicker COALESCE OR ISNULL?

Running the the following queries (in my machine):

DECLARE @ARG1 VARCHAR(50) = 'xserver_name'

select *
from sys.tables
where IsNull(@Arg1, name) = name

Cost: 27%

select *
from sys.tables
where Coalesce(@Arg1,name) = name

Cost 27%

select *
from sys.tables
where (@Arg1 is null or @Arg1 = name)

Cost 45%

Run and see the Execution plan please

But if the number of rows of the table increases, the difference disapear, because is is dominated by the table scan time:

select *
from sys.all_Columns
where IsNull(@Arg1, name) = name

Cost: 33%

select *
from sys.all_Columns
where Coalesce(@Arg1,name) = name

Cost 34%

select *
from sys.all_Columns
where (@Arg1 is null or @Arg1 = name)

Cost 33%

So can I ask regarding performance issue between the IsNull/Coalesce vs (@Arg1 is null or @Arg1 = Location) — Sid, Aug 20 '14 at 22:03
I think the performance difference is so slightly that the table needs to be small to perceive some difference, otherwise the time spent in these function will be ridiculous compared to the table scan. — Nizam, Aug 20 '14 at 22:16

Best way to do a CASE where clause SQL Server

3 Answers3

Linked