[SOLVED] Using "in" in where condition with case

Using "in" in where condition with case

I have a sql table which has one column "type". It has values A,B,C,D. I'm writing a stored procedure in which type is the in parameter @type.

@type can has either value 'A' or 'A,B,C' or 'ALL' based on user selection on screen. Which means user can select single,multiple or ALL options. I need to filter data from my table with condition on column "type".

I want something similar to below:

select * from maintable where
( case when @type ='ALL' then 1=1) else
 type in (select data from SplitString(@type,',')) end)

I have written a split function which return values in a table format.

When ALL is selected then the whole table should be returned. When specific type(s) is selected, only those types should be returned.

I'm using SQL Server 2012.

Solution

Try this

SELECT * 
FROM maintable 
WHERE @type ='ALL' OR    
(@type <>'ALL' AND TYPE IN (SELECT data FROM SplitString(@type,','))