CODEWITHSUNDEEP
sqlsql-server-2008where-clause
Null Head
Null Head

Reputation: 2941

Sql Server WHERE IN with CASE

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)

Upvotes: 2

Views: 14978

Answers (4)

Peter
Peter

Reputation: 1065

There are two approaches you can take with this one. My first option would be to use a sub-query or common table expression to invert the logic and return the product type, and then match on product type. The second would be to use 'sp_executesql'.

First option:

declare @ProductType int

WITH cte (Product_key, ProductType) AS (
    SELECT Product_key, CASE WHEN ProductLine IN ('TVs') THEN 1
        WHEN ProductLine IN ('Handsets', 'Mobiles') THEN 2  
        ELSE 3 END FROM Products
)
select p.* from Products p, cte 
where p.product_key = cte.product_key AND cte.ProductType = @ProductType

Second option:

declare @ProductType int, @sql NVARCHAR(MAX)

SET @sql = 'select * from Products 
    where ProductLine in (' +
        case @ProductType
            when 1 then '''TVs'''
            when 2 then '''Handsets'', ''Mobiles'''
            else '''Books'''
        end + ')'
EXEC sp_executesql @sql

Upvotes: 1

HABO
HABO

Reputation: 15816

CASE is an expression that returns a value. IN is a clause that may be part of a query. And SQL Server only grudgingly supports a boolean data type.

You can combine them thusly:

declare @ProductType int = 1
declare @Products as Table ( ProductLine VarChar(16) )
insert into @Products ( ProductLine ) values ( 'TVs' ), ( 'Books' )
select *
  from @Products  
  where
    case @ProductType 
      when 1 then ( select 1 where ProductLine in ('TVs') )
      when 2 then ( select 1 where ProductLine in ('Handsets', 'Mobiles') )
      else ( select 1 where ProductLine in ('Books') )
      end is not NULL

Upvotes: 0

Nick
Nick

Reputation: 1128

declare @ProductType int

select * from Products 
where (case @ProductType 
             when 1 then ProductLine in ('TVs') 
             when 2 then ProductLine in  ('Handsets', 'Mobiles') 
             else ProductLine in ('Books') end)

Upvotes: 2

zerkms
zerkms

Reputation: 254896

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))

Upvotes: 7

Related Questions