3

I've got a little problem with a query which I create in the Visual Studio Designer.

I need a query with a lot of 'OR'-clauses for the same column.

I found the operator 'IN', but I don't know how to use that in the Visual Studio Designer:

Example IN:

SELECT EmployeeID, FirstName, LastName, HireDate, City
FROM Employees
WHERE City IN ('Seattle', 'Tacoma', 'Redmond')

I tried to do it in this way:

SELECT [MachineryId], [ConstructionSiteId], [DateTime],
       [Latitude], [Longitude], [HoursCounter]
FROM [PositionData]
WHERE [MachineryID] IN @MachineryIDs

But this doesn't work.

Is there another way to handle a lot of OR clauses?

Thank you very much for your help.

Peter Lang
  • 54,264
  • 27
  • 148
  • 161
Silvan
  • 73
  • 1
  • 4
  • Check this SO question: [Parameterizing a SQL IN clause?](http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause) – zendar Mar 08 '10 at 09:05

1 Answers1

1

When doing an IN with a parameter it is used like below, fundamentally the same.

declare @SQL varchar(600)
set @SQL = 'select * from tbl where Cast([MachineryID] as varchar(255)) in ('+ @MachineryIDs +')'
EXEC(@SQL)
Dustin Laine
  • 37,935
  • 10
  • 86
  • 125
  • Thanks for your answer. Its very weird. When I try it with the @MachineryIDs parameter (and fill it with the values '1111','2222,'3333') it doesn't work. No rows are returned. If I remove the @MachineryIDs parameter and write [MachineryID] IN ('1111','2222','3333') it works fine. So there is something wrong with the parameter @MachineryIDs. Did you have the same problem? Thanks again ;) – Silvan Mar 08 '10 at 10:31
  • 1
    when you are using the @MachineryIDs the query probably resolves to: IN ('1111, 2222, 3333') - notice the ' only around the beginning and end of your string? It should be around every item in the list IN('1111', '2222', '3333') – Leslie Mar 08 '10 at 16:30