how use SQL WHERE CASE with NOT IN or equals at the same time?

Question

Hi all (my first post on the Stack!),

This works:

where
    Tran_date between @FromDate and @ToDate

and Range = @Range

and Store_ID =
    case when @Range = 'RangeName' then
        1234
    else
        Store_ID
    end

but how can I achieve this?:

where
    Tran_date between @FromDate and @ToDate

and Range = @Range

and Store_ID 
    case when @Range = 'RangeName' then
        not in (1234, 5678)
    else
        Store_ID
    end

score 5 · Answer 1 · answered Feb 13 '13 at 00:53

where
    Tran_date between @FromDate and @ToDate

and Range = @Range

and Store_ID 
    case when @Range = 'RangeName' AND Store_Id in (1234, 5678)
        9999 -- Assumes 9999 is a non possible value.  
             -- If it is possible then pick one that isn't.
    else
        Store_ID
    end

score 2 · Accepted Answer · answered Feb 12 '13 at 23:54

2

I think you want:

AND NOT (@Range = 'RangeName' AND Store_ID IN (1234,5678))

answered Feb 12 '13 at 23:54

GilM

3,711
17
18

score 0 · Answer 3 · answered Feb 12 '13 at 23:53

0

How about this:

where Tran_date between @FromDate and @ToDate
    and Range = @Range

    and (@Range = 'RangeName' and Store_ID not in (1234, 5678)
        or @Range <> 'RangeName' and Store_ID = Store_ID)

answered Feb 12 '13 at 23:53

bobs

21,844
12
67
78

how use SQL WHERE CASE with NOT IN or equals at the same time?

3 Answers3