How to Efficiently Pass a Dynamic String List to a SQL Server Stored Procedure from C#?

C# SQL Server - Passing a Dynamic String List to a Stored Procedure

Brett's query revolves around incorporating a dynamic list of strings into a SQL Server stored procedure. The challenge lies in efficiently passing this list without compromising performance.

The traditional approach of concatenating strings into a comma-separated list has limitations, especially with larger datasets. However, there are more efficient options available.

User Defined Table Type (UDT):

SQL Server 2008 introduced User Defined Table Types (UDT), which allows you to create a custom data type that can store tabular data. You can create a UDT called "StringList" and use it within your stored procedure.

SQL:

CREATE TYPE [dbo].[StringList] AS TABLE(
    [Item] [NVARCHAR](MAX) NULL
);

The following C# code demonstrates how to use this UDT:

var table = new DataTable();
table.Columns.Add("Item", typeof(string));

for (int i = 0; i < 10; i++)
    table.Rows.Add("Item " + i.ToString());

var pList = new SqlParameter("@list", SqlDbType.Structured);
pList.TypeName = "dbo.StringList";
pList.Value = table;

Within the stored procedure, you can access the list using:

CREATE PROCEDURE [dbo].[sp_UseStringList]
    @list StringList READONLY
AS
BEGIN
    SELECT l.Item FROM @list l;
END

The above is the detailed content of How to Efficiently Pass a Dynamic String List to a SQL Server Stored Procedure from C#?. For more information, please follow other related articles on the PHP Chinese website!