Boolean in case using sets sql in the where clause

Question

I'm trying to use Not In with a boolean variable in the where to determine whether or not to search in a set for something. For example:

    Select
      BLAH
    FROM
      BLAH
    WHERE
      ...
      AND CASE WHEN @variable = 'false' THEN
           warningcode1 NOT IN (101,102,103)

score 2 · Accepted Answer · answered Jan 11 '13 at 21:37

2

Try this

Select
      BLAH
    FROM
      BLAH
    WHERE
      ...
      AND (@variable <> 'false' OR warningcode1 NOT IN (101,102,103))

answered Jan 11 '13 at 21:37

rs.

26,707
12
68
90

score 2 · Answer 2 · answered Jan 11 '13 at 21:38

2

Just use:

AND (@variable <> 'false' OR  warningcode1 NOT IN (101,102,103))

So the warningcode1 matters only if the variable = 'false', othwerwise it will be ignored.

answered Jan 11 '13 at 21:38

Tim Schmelter

450,073
74
686
939

score 0 · Answer 3 · answered Jan 11 '13 at 21:42

0

try this with if

AND IF( @variable = 'false' , warningcode1 NOT IN (101,102,103) , warningcode1 IN (101,102,103) )

answered Jan 11 '13 at 21:42

echo_Me

37,078
5
58
78

Boolean in case using sets sql in the where clause

3 Answers3