Sql Server WHERE IN with CASE

sql, sql-server-2008, where-clause

Solution

You cannot do that - you need to split it to several checks:

WHERE (ProductLine = 'TVs' AND @ProductType = 1)
   OR (ProductLine IN ('Handsets', 'Mobiles') AND @ProductType = 2)
   OR (ProductLine = 'Books' AND @ProductType NOT IN (1, 2))

Problem

How can we specify range of values from CASE inside WHERE clause? This query of mine fails ``` declare @ProductType int select * from Products where ProductLine in ( case @ProductType when 1 then ('TVs') when 2 then ('Handsets', 'Mobiles') --fails here else ('Books') end ) ``` This wouldn't work either: ``` declare @ProductType int select * from Products where (case @ProductType when 1 then (ProductLine = 'TVs') when 2 then (ProductLine in ('Handsets', 'Mobiles')) else (ProductLine = 'Books') end) ```

Original source