c# - SQL Server (2008) Pass ArrayList or String to SP for IN()

Question

I was wondering how I can pass either an ArrayList, List<int> or StringBuilder comma delimited list to a stored procedure such that I find a list of IDs using IN():

@myList varchar(50)

SELECT  *
FROM    tbl
WHERE   Id IN (@myList)

In C# I am currently building the list as a string which is comma delimeted; however when using nvarchar(50) for example, as the type for the param in the stored procedure - I get an error as it can't convert '1,2,3' to int which it expects between the IN().

Any ideas? Much appreciated.
Pete

Answer 1

In SQL 2008 there are table-valued-parameters, that make a friendly alternative to parsing CSV; see here for an example.

Otherwise, another option is xml - the xml data type in SQL Server allows you to read this pretty easily (although it takes more transfer bytes).