c# - 'Must Declare the Scalar Variable' Error When Passing a Table-Valued Parameter to a Parameterized SQL Statement

Question

In C# I'm trying to pass a DataTable as a parameter to an SQL statement. The code I have is below:

protected virtual void DoDeleteRecords(List<Guid> ids)
{   
    if (ids.Count > 0)
    { 
        DataTable tvp = new DataTable();
        tvp.Columns.Add("Id", typeof(Guid));

        foreach (Guid id in ids)
        {
            DataRow row = tvp.NewRow();
            row["Id"] = id;

            tvp.Rows.Add(row);
        }

        string sql = string.Format("DELETE FROM MyTable WHERE ID IN ({0})", "@IDTable");

        SqlConnection connection = new SqlConnection(CoreSettings.ConnectionString);

        using (connection)
        {
            SqlCommand command = new SqlCommand(sql, connection);
            SqlParameter tvpParam = command.Parameters.AddWithValue("@IDTable", tvp);
            tvpParam.SqlDbType = SqlDbType.Structured;
            tvpParam.TypeName = "dbo.IDList";

            connection.Open();

            command.ExecuteNonQuery();

            connection.Close();
        }
    }
}

However, when command.ExecuteNonQuery is called, I get the following SqlException error:

Must declare the scalar variable "@IDTable"

I understand that this error is normally associated with missing out the parameter value, but as far as I can tell, I have that.

Can anyone see what I'm doing wrong?

Many thanks.

UPDATE I have modified the question to remove crappy SQL injection-rich code from my example.

Answer 1

First things first: I have no idea where you are getting the tableName and columnName, but if they are user-supplied, then this is open to SQL injection. At the very least, use QUOTENAME() to ensure no actual code is injected.

Secondly, you are not actually using the TVP. The code you have is just saying IN (@IDTable) which is not how you use a TVP.

A TVP is just a table variable, and should be used like any other table:

protected virtual void DoDeleteRecords(List<Guid> ids)
{   
    if (ids.Count == 0)
        return;
    DataTable tvp = new DataTable();
    tvp.Columns.Add("Id", typeof(Guid));

    foreach (Guid id in ids)
        tvp.Rows.Add(id);

    const string sql = @"
DELETE FROM table
WHERE idColumnName IN (SELECT * FROM @IDTable);
";

    using(SqlConnection connection = new SqlConnection(CoreSettings.ConnectionString))
    using(SqlCommand command = new SqlCommand(sql, connection))
    {
        command.Parameters.Add(
            new SqlParameter("@IDTable", SqlDbType.Structured)
        {
            Value = tvp,
            Direction = ParameterDirection.Input,
            TypeName = "dbo.IDList"
        });

        connection.Open();
        command.ExecuteNonQuery();
    }
}