CASE statement with IN clause in SQL SERVER

Question

How can I use CASE statement in my WHERE condition along with IN clause? I am trying following query and its giving syntax error.

SELECT * FROM tblCustomers
WHERE custCode = 'CST1'
AND   CASE @accountType WHEN 'Doemstic'
      THEN city IN ('hokkaido','tokyo')
      ELSE city IN ('mumbai')
      END

This statement gives syntax error near 'IN'

I am using SQL Server 2012

check this http://stackoverflow.com/questions/11232267/using-case-statement-inside-in-clause — Vinit Prajapati, Jun 19 '14 at 11:04

score 1 · Answer 1 · answered Jun 19 '14 at 11:07

when i have to do something similar, i try to write it in another way:

SELECT * FROM tblCustomers
WHERE 
custCode = 'CST1'
AND 
(
    (
        @accountType = 'Doemstic'
        AND 
        city IN ('hokkaido','tokyo')
    )
    OR
    (
        @accountType <> 'Doemstic'
        AND 
        city IN ('mumbai')
    )
)

if someone has a better idea, please share

CASE statement with IN clause in SQL SERVER

1 Answers1