case statement in where clause SQLSERVER

Question

I tried searching around but couldn't find anything that would help me out.

I'm trying to do this in SQL:

DECLARE @onlyMM INT

SET @onlyMM = 1

SELECT *
FROM cdn.magnag
WHERE CASE @onlyMM
        WHEN 1
            THEN man_zrdtyp NOT IN (
                    1616
                    ,2001
                    )
        ELSE - 1
        END

I have a problem with:

where case @onlyMM when 1 then man_zrdtyp not in (1616,2001) else -1 end

how to properly make a case for the operator not in?

It should be case when @onlyMM = 1 then man_zrdtyp not in (1616,2001) else -1 end — Dr. Stitch, Jan 19 '16 at 08:32
First of all what is else -1? You understand that your where CLAUSE is "where -1" now? — sagi, Jan 19 '16 at 08:34
[This answer](http://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297) demonstrates using `case` in a `join`, similar to a `where`. — HABO, Jan 19 '16 at 13:47

Arion · Answer 1 · 2016-01-19T08:37:55.417

6

I think you need to formulate the WHERE clause in a different way. You could use OR instead of case statement. Like this:

WHERE
    (@onlyMM=1 AND man_zrdtyp not in (1616,2001))
    OR @onlyMM<>1

edited Jan 19 '16 at 08:37

answered Jan 19 '16 at 08:32

Arion

31,011
10
70
88

`NOT @onlyMM=1` difficult to read. `@onlyMM<>1` I think is better. – Ruslan K. Jan 19 '16 at 08:36

score 0 · Accepted Answer · answered Jan 19 '16 at 08:32

0

You don't want to do this?

where (case when @onlyMM = 1 then man_zrdtyp else -1 end) not in (1616,2001)

answered Jan 19 '16 at 08:32

Gaston G

396
3
19

case statement in where clause SQLSERVER

2 Answers2