CODEWITHSUNDEEP
sqlsql-servert-sqlcasewhere-clause
Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

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

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')

Upvotes: 0

Views: 2751

Answers (3)

Martin Smith
Martin Smith

Reputation: 453037

An alternate method

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

Upvotes: 2

Rahul Tripathi
Rahul Tripathi

Reputation: 172408

Try this:

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

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460068

Is this what you want?

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

Upvotes: 8

Related Questions