How to use SQL WHERE CASE with NOT IN or equal at the same time?

Question

Hi everyone (my first post on Stack!),

It 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 

Answer 1

I think you want:

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

Answer 2

 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 

Answer 3

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)