WHERE IN (array of IDs)

Question

I have webservice which is passed an array of ints. I'd like to do the select statement as follows but keep getting errors. Do I need to change the array to a string?

[WebMethod] public MiniEvent[] getAdminEvents(int buildingID, DateTime startDate) {         command.CommandText = @"SELECT id,                             startDateTime, endDateTime From                             tb_bookings WHERE buildingID IN                             (@buildingIDs) AND startDateTime <=                             @fromDate";      SqlParameter buildID = new SqlParameter("@buildingIDs", buildingIDs); } 

Answer 1

You can't (unfortunately) do that. A Sql Parameter can only be a single value, so you'd have to do:

WHERE buildingID IN (@buildingID1, @buildingID2, @buildingID3...) 

Which, of course, requires you to know how many building ids there are, or to dynamically construct the query.

As a workaround*, I've done the following:

WHERE buildingID IN (@buildingID)  command.CommandText = command.CommandText.Replace(   "@buildingID",    string.Join(buildingIDs.Select(b => b.ToString()), ",") ); 

which will replace the text of the statement with the numbers, ending up as something like:

WHERE buildingID IN (1,2,3,4) 

• Note that this is getting close to a Sql injection vulnerability, but since it's an int array is safe. Arbitrary strings are not safe, but there's no way to embed Sql statements in an integer (or datetime, boolean, etc).

Answer 2

First you're going to need a function and a sproc. The function will split your data and return a table:

CREATE function IntegerCommaSplit(@ListofIds nvarchar(1000)) returns @rtn table (IntegerValue int) AS begin While (Charindex(',',@ListofIds)>0) Begin     Insert Into @Rtn      Select ltrim(rtrim(Substring(@ListofIds,1,Charindex(',',@ListofIds)-1)))     Set @ListofIds = Substring(@ListofIds,Charindex(',',@ListofIds)+len(','),len(@ListofIds)) end Insert Into @Rtn      Select  ltrim(rtrim(@ListofIds)) return  end 

Next you need a sproc to use that:

create procedure GetAdminEvents      @buildingids nvarchar(1000),     @startdate datetime as SELECT id,startDateTime, endDateTime From             tb_bookings t INNER JOIN  dbo.IntegerCommaSplit(@buildingids) i on i.IntegerValue = t.id  WHERE startDateTime <= @fromDate 

Finally, your code:

[WebMethod]         public MiniEvent[] getAdminEvents(int[] buildingIDs, DateTime startDate)         command.CommandText = @"exec GetAdminEvents";  SqlParameter buildID= new SqlParameter("@buildingIDs", buildingIDs); 

That goes way beyond what your question asked but it will do what you need.

Note: should you pass in anything that's not an int, the whole database function will fail. I leave the error handling for that as an exercise for the end user.