Techie BabajiLearn to Code. Build Real Skills.
ADO.NET · Advanced

Table-Valued Parameters

Need products for a list of Ids, or to save an order with many lines in one call? Table-valued parameters send a whole table as one parameter.

Advanced 5 min Lesson 24 of 32Examples use Microsoft.Data.SqlClient on .NET 9 with SQL Server 2022.
Before you start:Parameterized QueriesDataSet and DataTable
0/32 in ADO.NET
On this page · 5 min · Advanced

On this page

  1. In simple words
  2. Learning objectives
  3. Explanation
  4. Syntax
  5. Code example
  6. Step by step
  7. Real-world use
  8. Common mistakes
  9. Best practices
  10. Remember this
  11. Practice exercise
  12. Mini quiz
  13. Related topics
  14. My notes
Reading time
5 min
Difficulty
Advanced
Updated
Oct 29, 2026

Related tutorials

Interview questions

In simple words#

Instead of calling the shop ten times for ten items, you send one shopping list. A table-valued parameter sends a whole list as one parameter.

TVP
A table passed as a parameter.
CREATE TYPE … AS TABLE
Defines the list’s columns.
SqlDbType.Structured
How C# sends it.

Learning objectives#

  • Create a user-defined table type
  • Pass a DataTable as SqlDbType.Structured
  • Query with a TVP instead of dynamic IN lists
  • Use TVPs in stored procedures

Explanation#

Create a table type once in SQL Server: CREATE TYPE dbo.IdList AS TABLE (Id INT PRIMARY KEY).

In C#, fill a DataTable with matching columns and add a parameter with SqlDbType.Structured and TypeName = "dbo.IdList".

Join to the parameter like a table. This replaces building “IN (1,2,3)” strings, which are unsafe and produce a new plan for every length.

TVPs are read-only inside SQL and must be declared READONLY in procedures.

Syntax#

SQL Illustrative
CREATE TYPE dbo.IdList AS TABLE (Id INT PRIMARY KEY);

Code example: Get products for a list of Ids#

C#ProductsByIds.cs Illustrative
static async Task<List<string>> GetNamesAsync(SqlConnection conn, IEnumerable<int> ids)
{
    var tvp = new DataTable();
    tvp.Columns.Add("Id", typeof(int));
    foreach (var id in ids.Distinct()) tvp.Rows.Add(id);


    await using var cmd = new SqlCommand(
        "SELECT p.Name FROM Products p JOIN @ids i ON i.Id = p.Id ORDER BY p.Name", conn);
    cmd.Parameters.Add(new SqlParameter("@ids", SqlDbType.Structured) { TypeName = "dbo.IdList", Value = tvp });


    var names = new List<string>();
    await using var r = await cmd.ExecuteReaderAsync();
    while (await r.ReadAsync()) names.Add(r.GetString(0));
    return names;
}


Console.WriteLine(string.Join(", ", await GetNamesAsync(conn, new[] { 3, 1, 3 })));
Expected output
Keyboard, Laptop

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

  1. The DataTable has one Id column matching the type.
  2. Distinct avoids violating the type’s primary key.
  3. The SQL joins to @ids as if it were a table.
  4. TypeName tells SQL Server which table type the parameter is.

Real-world use case#

A shopping cart page loads all its products in one query by passing the cart’s product Ids as a TVP.

Common mistakes#

Building IN lists with string.Join

That is string concatenation — injection-prone and bad for plan caching.

Column order mismatch

TVP columns are matched by position. Keep the DataTable columns in the same order as the type.

Best practices#

  • Use TVPs for lists of values
  • Add a primary key to the type
  • Declare TVP parameters READONLY in procedures
  • For thousands of rows of inserts, compare with SqlBulkCopy

Remember this#

CREATE TYPE … AS TABLE once.

SqlDbType.Structured + TypeName.

JOIN @list instead of IN (string).

Practice exercise#

Your task: Write a stored procedure dbo.DeleteProducts that accepts @Ids dbo.IdList and deletes those products.

Show solution
SQL Illustrative
CREATE OR ALTER PROCEDURE dbo.DeleteProducts @Ids dbo.IdList READONLY
AS
BEGIN
  SET NOCOUNT ON;
  DELETE p FROM Products p JOIN @Ids i ON i.Id = p.Id;
END

Mini quiz#

Table-Valued Parameters quizMultiple choice Question 1 of 1

Which SqlDbType is used for a table-valued parameter?

Score so far: 0/0

Want more? Practise all ADO.NET MCQs · ADO.NET interview questions

My notes#

Saved in this browser.

Progress is saved in this browser. Sign in to sync.