Best Way To Do A Case Where Clause Sql Server

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

Solution 1:

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.

Solution 2:

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

whereCOALESCE(@Arg1, Location) = Location
  andCOALESCE(@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).

Solution 3:

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@ARG1VARCHAR(50) ='xserver_name'select*from sys.tables
where IsNull(@Arg1, name) = name

Cost: 27%

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

Cost 27%

select *
fromsys.tableswhere (@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
whereIsNull(@Arg1, name) = name

Cost: 33%

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

Cost 34%

select *
fromsys.all_Columnswhere (@Arg1 is null or @Arg1 = name)

Cost 33%