How to use List<int> as SQL parameter in C# [duplicate]

Question

I am trying to use List< int> as SQL parameter using this code:

var listID= new List<int>() { 1, 2, 3, 4, 5, 6, 7, 8, 9 };

        using (var sqlConnection = new SqlConnection(_connectionstring))
        {
            using (var cmd = new SqlCommand())
            {
                cmd.Connection = sqlConnection;
                cmd.CommandText = "delete from MyTable where TableID in ( @tableID)";

                string param = String.Join(",", listID.ToArray());
                cmd.Parameters.Add("@tableID", param);
                sqlConnection.Open();
                cmd.ExecuteNonQuery();
            }
            sqlConnection.Close();
        }

The problem is, that this code will generate:

exec sp_executesql N'delete from MyTable where TableID in ( @tableID)',N'@tableID nvarchar(17)',@tableID =N'1,2,3,4,5,6,7,8,9'

This will fail because:

Conversion failed when converting the nvarchar value '1,2,3,4,5,6,7,8,9' to data type int.

Any idea how to solve this? Thank you.

EDIT: I'm using MS SQL 2012

Answer 1

You should use a TVP, than you can keep the query exactly as specified. They were introduced in SQL 2008.

Table Valued Parameter, with example