CASE in WHERE SQL Server

Question

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  gt  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

User · Answer

something else  should be a Country  if Country is nullable then make something else  be a Country OR a Country is NULL

User · Answer

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  gt  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

User · Answer

Try this   WHERE a Country    CASE WHEN  Country  gt  0 THEN  Country ELSE a Country END

User · Answer

ELSE a Country       I suppose

User · Answer

You can simplify to   WHERE a Country   COALESCE NULLIF  Country 0   a Country

[sql] CASE in WHERE, SQL Server

Examples related to sql

Examples related to sql-server

Examples related to case

Examples related to where