Using "in" where the condition is with the case

Question

I have a sql table that has one type column. It has values ​​A, B, C, D.
I am writing a stored procedure in which the type is the @type parameter.
@type can be either “A” or “A”, “B”, “C” or “ALL” based on the user's choice on the screen. This means that the user can select one, several or ALL options.
I need to filter data from my table with a condition in the Type column.

I need something similar below

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

I wrote a split function that returns values ​​in a table format.

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

I am using sqlserver 2012.
Please help!

Answer 1

try it

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

Answer 2

You can do this as shown below:

if @type ='ALL' 
    set @type ='A,B,C,D'

select * from maintable where
type in (select data from SplitString(@type, ','))

Answer 3

You can use IF:

IF @type ='ALL'
    SELECT *
    FROM MainTable
        ELSE
            SELECT *
            FROM MainTable
            WHERE Type IN (SELECT data FROM SplitString(@type,',') )