CODEWITHSUNDEEP
sqlsql-servercasewhere-clause
el ninho
el ninho

Reputation: 4233

CASE in WHERE, SQL Server

I'm doing some search, where users are choosing in dropdown some clauses. When they leave some box empty, I want query to ignore clause. I know CASE, and best I thought of is that if I pass 0 to parameter in stored procedure, it ignores that parameter, like this.

WHERE a.Country = (CASE WHEN @Country > 0 THEN @Country ELSE (something else) END)

so, (something else) should be like no condition, it can be '>0' as country ids are from all >1, but I don't know how to use > and = in same CASE.

Any suggestions?

Upvotes: 23

Views: 149468

Answers (5)

SliverNinja - MSFT
SliverNinja - MSFT

Reputation: 31641

(something else) should be a.Country

if Country is nullable then make(something else) be a.Country OR a.Country is NULL

Upvotes: 3

AdaTheDev
AdaTheDev

Reputation: 147224

A few ways:

-- Do the comparison, OR'd with a check on the @Country=0 case
WHERE (a.Country = @Country OR @Country = 0)

-- compare the Country field to itself
WHERE a.Country = CASE WHEN @Country > 0 THEN @Country ELSE a.Country END

Or, use a dynamically generated statement and only add in the Country condition if appropriate. This should be most efficient in the sense that you only execute a query with the conditions that actually need to apply and can result in a better execution plan if supporting indices are in place. You would need to use parameterised SQL to prevent against SQL injection.

Upvotes: 36

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

You can simplify to:

WHERE a.Country = COALESCE(NULLIF(@Country,0), a.Country);

Upvotes: 5

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79919

Try this:

WHERE a.Country = (CASE WHEN @Country > 0 THEN @Country ELSE a.Country END)

Upvotes: 2

tanathos
tanathos

Reputation: 5606

.. ELSE a.Country ...

I suppose

Upvotes: 1

Related Questions