Passing lists to a SPROC - excellent article

Arrays and Lists in SQL Server 2005 and Beyond

In the public forums for SQL Server, you often see people asking How do I use arrays in SQL Server? Or Why does SELECT * FROM tbl WHERE col IN (@list) not work? The short answer to the first question is that SQL Server does not have arrays – SQL Server has tables. However, upto SQL 2005 you could not specify a table as input to SQL Server from a client, but you had to pass a string with the values and unpack it into a table on the SQL Server end.

This article describes a number of different ways to do this, both good and bad. I first give a background to the problem (including a quick solution that is good enough in many cases). I then give a brief overview over the methods, whereupon I discuss general issues that apply, no matter which method you use. Having dealt with these introductory topics, I devote the rest of the article to detailed descriptions of all methods, and I discuss their strengths and weaknesses.