‘Must Declare the Scalar Variable’ Error When Passing a Table-Valued Parameter to a Parameterized SQL Statement

by

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();
    }
}

More Related Contents:

  1. How to pass table value parameters to stored procedure from .net code
  2. How can I insert 10 million records in the shortest time possible?
  3. How to run multiple SQL commands in a single SQL connection?
  4. run .sql file over a collection of databases on an instance
  5. How to pass an array into a SQL Server stored procedure
  6. Call a stored procedure with parameter in c#
  7. Execute a large SQL script (with GO commands)
  8. Calling stored procedure with return value
  9. Cannot open database “test” requested by the login. The login failed. Login failed for user ‘xyz\ASPNET’
  10. Using StringWriter for XML Serialization
  11. Entity framework very slow to load for first time after every compilation
  12. Avoiding SQL injection without parameters
  13. How can I lock a table on read, using Entity Framework?
  14. Using DateTime in a SqlParameter for Stored Procedure, format error
  15. Code to validate SQL Scripts
  16. How do I perform an insert and return inserted identity with Dapper?
  17. Creating a database programmatically in SQL Server
  18. How can I call a SQL Stored Procedure using EntityFramework 7 and Asp.Net 5
  19. Optimistic concurrency: IsConcurrencyToken and RowVersion
  20. ALTER TABLE DROP COLUMN failed because one or more objects access this column
  21. When executing a stored procedure, what is the benefit of using CommandType.StoredProcedure versus using CommandType.Text?
  22. Can I use ADFS 2.0 to authenticate certain users against SQL Server?
  23. Can’t access my SQL Server via C# form application from client device
  24. Filling missing dates by group
  25. Reverse engineering SSIS package using C#
  26. Dynamic query with OR conditions in Entity Framework
  27. How to pass a null variable to a SQL Stored Procedure from C#.net code
  28. Is there a .NET equivalent to SQL Server’s newsequentialid()
  29. How to Specify Primary Key Name in EF-Code-First
  30. Subset sum problem

Leave a Comment