sqlParameter conversion

I'm trying to call the sql statement below but get the following error.

System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value '+@buildingIDs+' to data type int.

@"SELECT id, startDateTime, endDateTime From tb_bookings WHERE buildingID IN ('+@buildingIDs+') AND startDateTime <= @fromDate";

buildingID is an int type column in the db. Will I need to pass the IDs as an array of ints?

Thanks Barry

From stackoverflow

• It's trying to compare an int with the string value '+@buildingsIDs+'
  So it tries to convert the string to convert it to an int and fails.

  So do the following:
  buildingsIDs = "1, 5, 6";
@"SELECT id, startDateTime, endDateTime From tb_bookings WHERE buildingID IN (" + buildingIDs + ") AND startDateTime <= @fromDate";

• Bravax's way is a bit dangerous. I'd go with the following so you don't get attacked with SQL Injections:

  int[] buildingIDs = new int[] { 1, 2, 3 };
  
  /***/ @"SELECT id, startDateTime, endDateTime From tb_bookings WHERE buildingID IN (" +
        string.Join(", ", buildingIDs.Select(id => id.ToString()).ToArray())
        + ") AND startDateTime <= @fromDate";
  

  Bravax : To be honest, I can't see the difference in the code. We're both building a string and executing it. You've just fleshed out how you would construct the buildingIDs string. If SQL Injection is an issue, then use a stored procedure, possibly using Marc Gravell's approach.

  Chris Shaffer : The difference is small but significant - Your code example implied string concatenation using a passed in string(which introduces the possibility of SQL Injection); This example passes in an integer array, essentially forcing clean data to be passed to it.

• I would rather suggest to go for a stored procedure, if possilble, and pass the lists of IDs as xml. You can get more details on this approach from here.

• Note that LINQ can do this via Contains (which maps to IN). With regular TSQL, another option is to pass down the list as a CSV (etc) varchar, and use a table-valued UDF to split the varchar into pieces. This allows you to use a single TSQL query (doing an INNER JOIN to the UDF result).

• This is almost exactly the same ? as http://stackoverflow.com/questions/182060/sql-where-in-array-of-ids