10774: alternatives to the OR sentence

Hi experts:

i have this query
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT
      [companyname]
      ,[contactname]
      ,[contacttitle]
      ,[address]
      ,[city]
      ,[country]
      ,[phone]
      ,[fax]
  FROM [TSQL2012].[Production].[Suppliers]
  where city = 'Tokyo' OR city = 'Osaka' OR city = 'London'

The user may enter 1, 2, 3 or n city to do the search. I understand that using OR is not advisable, so as I can do?
enrique_aeoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
Put the city name(s) into a table keyed on city and do an INNER JOIN.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
"OR" is possible, but it depends on the number of cities the user can select from. If it's limited, then OR is fine. If not, then either another table or a variable/parameter would be the way to go.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

enrique_aeoAuthor Commented:
Please

I need transact sql code.

is more optimal use "IN" or use "OR" ?. In this case I can have up to 35 different city values
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I've used the table and JOIN method that Scott recommends with success in my previous life as an Access developer.
Doing it that way the SQL query does not have to change, and most of the work will be in the UI populating and clearing the table based on the user selections.
SELECT 
   s.companyname, s.contactname, s.contacttitle,
   s.address, s.city, s.country, s.phone, s.fax
FROM Suppliers s
   JOIN ui_city_choices ui ON s.companyname = ui.companyname

Open in new window

>is more optimal use "IN" or use "OR" ?.
Without the UI part IN would be easier to code after about five selections, BUT with the UI part it would be an equal pain to have to create code that parses a SQL statement that  loops through all user selections and either add an OR.. or the values within an IN block.  Just my opinion.
0
Scott PletcherSenior DBACommented:
There's no function difference between "IN" and "OR", zilch.  Because, in fact, SQL converts the "IN" to a string of "ORs" (you can verify this by looking at the query plan).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.