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

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

If you are using SQL Server 2008, it has new feature called the User Defined Table Type. 

For example:

You can create your User Defined Table Type like this:

CREATE TYPE [dbo].[StringList] AS TABLE(

    [Item] [NVARCHAR](MAX) NULL

);

Now, you can use it properly in your stored procedure this way:

CREATE PROCEDURE [dbo].[sp_UseStringList]

    @list StringList READONLY

AS

BEGIN

    -- Just return the items we passed in

    SELECT l.Item FROM @list l;

END

Some sql to use in c# are as follows:

using (var con = new SqlConnection(connstring))

{

    con.Open();

    using (SqlCommand cmd = new SqlCommand("exec sp_UseStringList @list", con))

    {

        using (var table = new DataTable()) {

          table.Columns.Add("Item", typeof(string));

          for (int i = 0; i < 10; i++)

            table.Rows.Add("Item " + i.ToString());

            var pList = new SqlParameter("@list", SqlDbType.Structured);

          pList.TypeName = "dbo.StringList";

          pList.Value = table;

          cmd.Parameters.Add(pList);

          using (var dr = cmd.ExecuteReader())

          {

            while (dr.Read())

                Console.WriteLine(dr["Item"].ToString());

          }

         }

    }

}

If you want to execute this from SSMS, then use this:

DECLARE @list AS StringList

INSERT INTO @list VALUES ('Apple')

INSERT INTO @list VALUES ('Banana')

INSERT INTO @list VALUES ('Orange')

-- Otherwise, you can populate @list with an INSERT-SELECT like this:

INSERT INTO @list

   SELECT Name FROM Fruits

EXEC sp_UseStringList @list

You can learn in-depth about SQL statements, queries and become proficient in SQL queries by enrolling in our industry-recognized SQL course.