Passing List`<>` to SQL Stored Procedure

sql

table-valued-parameters

performance-optimization

sql-server

byNikita Barsukov·Sep 8, 2024

Table-Valued Parameters (TVPs) are your best bet when you need to pass a List<> to a SQL Stored Procedure. Here's the process boiled down to three steps:

Declare a table type in SQL:

CREATE TYPE dbo.ListType AS TABLE (Value INT); --Ready to store your numbers!

Use the TVP in your Stored Procedure:

CREATE PROCEDURE dbo.InsertList @List dbo.ListType READONLY AS BEGIN
    --Insert magic happens here!
    INSERT INTO YourTable SELECT * FROM @List; 
END;

Populate a DataTable in C# from your list and send it to the Stored Procedure:

DataTable table = new DataTable(); // new table, who dis?
table.Columns.Add("Value", typeof(int)); // adding a column for our numbers
foreach(var item in yourList) {
    table.Rows.Add(item); // populating the table, item by item
}
SqlParameter param = new SqlParameter {
    ParameterName = "@List",
    SqlDbType = SqlDbType.Structured,
    TypeName = "dbo.ListType",
    Value = table // table is going on a trip to SQL Server
};
SqlCommand command = new SqlCommand("InsertList", connection) {
    CommandType = CommandType.StoredProcedure // knock, knock! Who's there? It's a stored procedure call!
};
command.Parameters.Add(param);
command.ExecuteNonQuery(); // and... ACTION!

This approach not only makes your code neater, but also optimizes performance, saving considerable time otherwise spent in dealing with string parsing.

Squeeze the juice out of table-valued parameters

Moving to SQL Server 2008 or later lets you use a not-so-secret weapon: table-valued parameters. This feature can boost your performance when passing a List<> to SQL Server. Here’s why you should not skip this upgrade:

Batch operations: Forget about looping through lists. Batch insert all your items in one go with TVPs.
Side step string splitting: Avoid the inefficiency of dealing with string parsing by directly using TVPs in stored procedures.
Best friend of report generation: Mass operations like generating complex reports become a piece of cake with TVPs.

Dealing with data in big chunks

Table-valued parameters shine when they are thrown big chunks of data:

Reducing roundtrips: Deliver a large shopping list to SQL Server in a single trip.
Server got your back: SQL Server side transactions ensure all your items get inserted together or not at all.
Concurrency better than a coffee shop: Reducing contention with fewer locks and blockages, TVPs make sure other operations don't need to wait in line.

No TVPs? No problem!

TVPs are a first-rate choice, but here are some alternatives:

JSON/XML: Still hanging on to an old SQL Server version or want to handle non-relational data? JSON or XML might come to your rescue, though they come with parsing overhead.
Bulk copy: SqlBulkCopy is a good choice for massive inserts, although it’s not as flexible as TVPs within stored procedures.