Using "in" in where condition with case

Question

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.
Kindly help!!

How about using `IF` in your `SP` – Ilyes Dec 13 '17 at 07:50 — Ilyes, Dec 13 '17 at 07:50

score 3 · Accepted Answer · edited Dec 13 '17 at 07:51

3

Try this

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

edited Dec 13 '17 at 07:51

Danieboy

4,393
6
32
57

answered Dec 13 '17 at 07:45

Jayasurya Satheesh

7,826
3
22
39

It's `STRING_SPLIT()` not `SplitString()` – Ilyes Dec 13 '17 at 08:06
@Sami he says this is a UDF that he wrote – Jayasurya Satheesh Dec 13 '17 at 08:10
Oh yes my bad :) – Ilyes Dec 13 '17 at 08:11
Thanks a lot..It worked.It is so simple and perfect!! – CrazyCoder Dec 13 '17 at 09:02
@Sami , when i tried to use STRING_SPLIT(), it is saying invalid object. That is the reason I have created my own function SplitString(). – CrazyCoder Dec 13 '17 at 09:03
STRING_SPLIT() is available for databases with compatibility level 130 or above. This is connected with the version of SQL Server (but even in the new SQL server there can be databases with low compatibility level): https://learn.microsoft.com/en-us/sql/relational-databases/databases/view-or-change-the-compatibility-level-of-a-database?view=sql-server-2017 – Adrian Stanisławski Oct 04 '18 at 14:07

score 0 · Answer 2 · answered Dec 13 '17 at 07:54

0

You can do it like below :

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

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

answered Dec 13 '17 at 07:54

Md. Suman Kabir

5,243
5
25
43

Ilyes · Answer 3 · 2017-12-13T08:13:16.763

0

You can use an IF:

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

edited Dec 13 '17 at 08:13

answered Dec 13 '17 at 07:57

Ilyes

14,640
4
29
55

Using "in" in where condition with case

3 Answers3