T-SQL. CASE expression in WHERE clause using IN operator

Question

If user put 800 as @WorkShop It should return all records having 800 workshop Id and 900 workshop id.

If user pass any other Id as @WorkShop It should return only records for choosen @WorkShop Id.

I have tried in following:

SELECT *
FROM Test
WHERE Workshop IN ( 
                    CASE 
                        WHEN @WorkShop = N'800' THEN N'900' AND N'800' 
                        ELSE @WorkShop 
                    END 
                    )

This returns an error:

Incorrect syntax near the keyword 'and'.

So in conclusion if user pass 800 as @WorkShop parameter It should return something like:

Workshop IN ('800', '900')

If you do need to use a `CASE` expression in a `WHERE` (or `JOIN`/`ON`) [this](http://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297) may be helpful. — HABO, Jun 01 '15 at 15:31

score 8 · Accepted Answer · answered Jun 01 '15 at 13:07

8

Is this what you want?

SELECT *
FROM Test
WHERE ( @WorkShop = '800' AND  Workshop IN('800', '900') )
OR @WorkShop =  Workshop

answered Jun 01 '15 at 13:07

Tim Schmelter

450,073
74
686
939

score 2 · Answer 2 · answered Jun 01 '15 at 14:17

2

An alternate method

SELECT *
FROM   Test
WHERE  Workshop IN ( @WorkShop, CASE
                                  WHEN @WorkShop = N'800' THEN N'900'
                                END )

answered Jun 01 '15 at 14:17

Martin Smith

438,706
87
741
845

score 1 · Answer 3 · answered Jun 01 '15 at 13:08

1

Try this:

SELECT *
FROM Test
WHERE (@WorkShop = '800' AND  (Workshop = '800' or  Workshop = '900'))
OR @WorkShop =  Workshop

answered Jun 01 '15 at 13:08

Rahul Tripathi

168,305
31
280
331

T-SQL. CASE expression in WHERE clause using IN operator

3 Answers3