sqlParameter conversion

Question

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

Answer 1

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";

Answer 2

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";

Answer 3

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.

Answer 4

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).

Answer 5

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