Link to home
Start Free TrialLog in
Avatar of chenyuhao88
chenyuhao88

asked on

MSSQL "in" clause

Dear All,

Assume I have a simple query like:

Select * from Stationery
where type in ('pen','rubber')


And now I try to replace ('pen','rubber') with another kind of expression which could represent everything.

How could I do that? (instead of removing where clause if possible)

Thanks heaps!
Avatar of chenyuhao88
chenyuhao88

ASKER


Or if it's not possible and I have query like this:


Select * from Stationery
where price >20 and
type in ('pen','rubber')


how could I use ie "case...when..." to skip filter "type in ('pen','rubber')" if the type list is empty?

ASKER CERTIFIED SOLUTION
Avatar of appari
appari
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Appari,

Actually "type in ('pen','rubber')" is part of my stored procedure.

"type in (@para1)" is already there and i don't want to change it. And I need to set proper parameter to represent ALL type if no "type-filter". Or just skip that "and clause".




can you post your procedure code?
I follow your hint and now it works fine!