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?
This question is related to
sql
sql-server
case
where
(something else)
should be a.Country
if Country
is nullable then make(something else)
be a.Country OR a.Country is NULL
.. ELSE a.Country ...
I suppose
Try this:
WHERE a.Country = (CASE WHEN @Country > 0 THEN @Country ELSE a.Country END)
You can simplify to:
WHERE a.Country = COALESCE(NULLIF(@Country,0), a.Country);
Source: Stackoverflow.com