ADO.NET parameters for use with the SQL “IN” Keyword
by Warlock on Sep.11, 2008, under .NET, Database
Long of the short of it is there isn’t a good way to do it.
A common example is that you might want select multiple rows from a table, using a list of IDs:
SELECT * FROM room WHERE room_id IN (1,2,3,4);
What you’d like to do is have this list as an array parameter and set it appropriately on the command:
int ids = new int[] { 1, 2, 3, 4 };
string sql = "SELECT * FROM room WHERE room_id IN @IDS;";
SqlCommand cmd = new SqlCommand(sql, conn);
// DOES NOT WORK!
cmd.Parameters.Add("@IDS", ids);
The problem is there is no array type in SQL server/ADO.NET. The only way to accomplish this is through dynamic sql, which is definitely not a best practice.
There are several articles discussing this problem. The following are taken from this forum discussion in which Arnie Rowland provided the links.
- http://www.sommarskog.se/arrays-in-sql.html
- http://databases.aspfaq.com/database/how-do-i-simulate-an-array-inside-a-stored-procedure.html
- http://www.projectdmx.com/tsql/sqlarrays.aspx
- http://omnibuzz-sql.blogspot.com/2006/06/interesting-queries-using-recursive.html
- http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
- http://msdn.microsoft.com/en-us/library/ms188332.aspx
- http://msdn.microsoft.com/en-us/library/ms175170.aspx
