Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

CASE in WHERE, SQL Server

Tags:

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?

like image 837
el ninho Avatar asked Apr 17 '12 12:04

el ninho


2 Answers

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.

like image 78
AdaTheDev Avatar answered Oct 09 '22 02:10

AdaTheDev


You can simplify to:

WHERE a.Country = COALESCE(NULLIF(@Country,0), a.Country); 
like image 25
Aaron Bertrand Avatar answered Oct 09 '22 02:10

Aaron Bertrand