How Can I Efficiently Pass a List to a SQL Stored Procedure?

Passing List to SQL Stored Procedure Efficiently

Traditionally, passing multiple items to a SQL stored procedure involved tedious string manipulation and concatenating IDs separated by delimiters. This approach can be suboptimal in terms of performance and flexibility. However, with the advent of SQL Server 2008, a new feature called "table-valued parameters" emerged as a superior solution.

By utilizing table-valued parameters, developers can define a user-defined table type in the database and then pass a list of values of that type as a single parameter to the stored procedure. This approach eliminates the need for manual string concatenation and provides several benefits:

• Improved performance: Table-valued parameters allow the database to handle the data manipulation more efficiently, resulting in faster execution times.
• Simplified code: The code becomes more concise and readable, as it no longer requires complex string manipulation operations.
• Increased flexibility: Table-valued parameters support more complex data types and allow for additional processing within the stored procedure.

To leverage table-valued parameters, developers need to create a user-defined table type in the database and then modify the stored procedure to accept this type as a parameter. The code snippet below demonstrates how to implement this approach:

C# Code:

public void AddItemsToReport(string connStr, int Id, List<int> itemList)
{
    using (SqlConnection _connection = new SqlConnection(connStr))
    {
        using (SqlCommand _sqlcommand = new SqlCommand("AddItemsToReport", _connection))
        {
            _sqlcommand.CommandType = CommandType.StoredProcedure;

            _sqlcommand.Parameters.AddWithValue("ReportId", Id);
            _sqlcommand.Parameters.AddWithValue("Items", itemList);

            _connection.Open();
            _sqlcommand.ExecuteNonQuery();
        }
    }
}

SQL Stored Procedure:

CREATE PROCEDURE AddItemsToReport (@ReportId int, @Items IntArray)
AS
BEGIN
   -- Insert each item into ReportItem table
   INSERT INTO ReportItem (ReportId, ItemId)
   SELECT @ReportId, Item
   FROM @Items
END

In this example, the "IntArray" type is a user-defined table type that maps to the List in C#. The "@Items" parameter can then accept the list of item IDs as a single entity, enhancing both performance and code readability.

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