How to Pass a C# String List to an SQL Server Stored Procedure's IN Clause?

Passing Lists to SQL Server Stored Procedures from C#

In the given C# code, a stored procedure is being invoked with various parameter values, including a list of strings. However, the query within the stored procedure requires the list to be passed as part of the IN clause.

Solution: Using User Defined Table Types (SQL Server 2008 )

SQL Server 2008 introduced User Defined Table Types, a feature that allows passing collections of data to stored procedures in a structured manner. Here's how to implement this solution:

1. Create a User Defined Table Type

Create a table type that corresponds to the list of strings. For instance:

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

2. Modify the Stored Procedure

Adapt the stored procedure to accept the table type as an input parameter:

CREATE PROCEDURE [dbo].[sp_UseStringList]
    @list StringList READONLY
AS
BEGIN
    -- Query using the table type
    SELECT l.Item FROM @list l;
END

3. Populate the Table Type in C#

In C#, create a DataTable object and populate it with the list of strings. Then, define a parameter to pass the DataTable to the stored procedure.

using (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;

    cmd.Parameters.Add(pList);
}

4. Execute the Stored Procedure

Execute the stored procedure with the populated SqlParameter object.

Example Usage:

using (var con = new SqlConnection(connstring))
{
    con.Open();

    using (SqlCommand cmd = new SqlCommand("exec sp_UseStringList @list", con))
    {
        using (var dr = cmd.ExecuteReader())
        {
            while (dr.Read())
                Console.WriteLine(dr["Item"].ToString());
        }
    }
}

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