CASE in WHERE inside stored procedure

Question

I want to set 2 different where conditions depending on the value of a variable, that is:

if @var=0 then Where fieldFecha='1900/01/01' and if @var=1 then where fieldfecha<>'1900/01/01'
    DECLARE @var int
    SET @var=0
    SELECT * FROM Table
    WHERE fieldFecha=CASE ?????

I don't know what to put in the CASE because I need to use fieldFecha= and fielFecha<>

Any idea please?

Regards

Don't use a `CASE` expression in there `WHERE` at all; use proper boolean logic. A `CASE` won't be SARGable. — Thom A, Mar 06 '20 at 09:11
[This](https://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297) answer explains how to use `case` in an `on` clause and is applicable to `where`. It is rarely the best approach as others have noted. — HABO, Mar 06 '20 at 14:22

score 3 · Answer 1 · answered Mar 06 '20 at 09:30

3

Try to something like

 DECLARE @var int = 0;
 SELECT * FROM Table
 WHERE (@var=0 AND fieldFecha='1900/01/01') OR (@var=1 AND fieldfecha<>'1900/01/01')

Also, it depends on what you needs, but in this case you can use UNION

answered Mar 06 '20 at 09:30

Roman Marusyk

23,328
24
73
116

When you use AND, OR clauses, please use brackets for those completely like ((@var=0 AND fieldFecha='1900/01/01') OR (@var=1 AND fieldfecha<>'1900/01/01')). or else if the user adds some extra filters, it would work wrong then. – Arulmouzhi Mar 06 '20 at 18:19
I think, if the user adds some extra filters, then the user can add more brackets. In this case, it is not necessary, and it doesn't related to the question – Roman Marusyk Mar 07 '20 at 23:31

CASE in WHERE inside stored procedure

1 Answers1