C# SQL Server - Passing a list to a stored procedu

Question

I am calling a SQL Server stored procedure from my C# code:

using (SqlConnection conn = new SqlConnection(connstring))
{
   conn.Open();
   using (SqlCommand cmd = new SqlCommand("InsertQuerySPROC", conn))
   {
      cmd.CommandType = CommandType.StoredProcedure;

      var STableParameter = cmd.Parameters.AddWithValue("@QueryTable", QueryTable);
      var NDistanceParameter = cmd.Parameters.AddWithValue("@NDistanceThreshold", NDistanceThreshold);
      var RDistanceParameter = cmd.Parameters.AddWithValue(@"RDistanceThreshold", RDistanceThreshold);

      STableParameter .SqlDbType = SqlDbType.Structured;
      NDistanceParameter.SqlDbType = SqlDbType.Int;
      RDistanceParameter.SqlDbType = SqlDbType.Int;

      // Execute the query
      SqlDataReader QueryReader = cmd.ExecuteReader();

My stored proc is fairly standard but does a join with QueryTable (hence the need for for using a stored proc).

Now: I want to add a list of strings, List<string>, to the parameter set. For example, my stored proc query goes like this:

SELECT feature 
FROM table1 t1 
INNER JOIN @QueryTable t2 ON t1.fid = t2.fid 
WHERE title IN <LIST_OF_STRINGS_GOES_HERE>

However, the list of strings is dynamic and a few hundred long.

Is there a way to pass a list of strings List<string> to the stored proc??? Or is there a better way to do this?

Many thanks, Brett

Answer 1

Yep, make Stored proc parameter as VARCHAR(...) And then pass comma separated values to a stored procedure.

If you are using Sql Server 2008 you can leverage TVP (Table Value Parameters): SQL 2008 TVP and LINQ if structure of QueryTable more complex than array of strings otherwise it would be an overkill because requires table type to be created within SQl Server