Share
Go down
avatar
Admin
Posts : 155
Join date : 2017-11-11
Age : 27
View user profilehttp://net-media.yoo7.com

Using “in” in where condition with case

on Wed Dec 13, 2017 11:35 am
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
Code:
select * from maintable where
( case when @type ='ALL' then 1=1) else
 type in (select data from SplitString(@type,',')) end)

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 sqlserver 2012.

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

You can use an IF:
Code:
IF @type ='ALL'
    SELECT *
    FROM MainTable
        ELSE
            SELECT *
            FROM MainTable
            WHERE Type IN (SELECT data FROM SplitString(@type,',') )

You can do it like below :
Code:
if @type ='ALL'
    set @type ='A,B,C,D'

select * from maintable where
type in (select data from SplitString(@type, ','))
Back to top
Permissions in this forum:
You can reply to topics in this forum