How Can Table-Valued Parameters Improve Passing Lists to SQL Stored Procedures?

Passing List<> to SQL Stored Procedure: A Better Approach

As often encountered, loading multiple items to a particular database record can present challenges. Consider a scenario where a web page allows users to select items for a report, and these items correspond to records in a database (Report and Item tables, respectively). Upon submission, the database updates with the selected items added to the ReportItems table (ReportId, ItemId).

Traditionally, handling this involves a SQL command and stored procedure. However, the introduction of Table-valued Parameters (TVPs) in SQL Server 2008 provides a superior solution.

Table-valued Parameters

TVPs allow passing a list of items to a stored procedure as a single dataset, eliminating the need for string concatenation and repeated parameter insertions. Instead, the TVP is defined as a table type and received in the stored procedure.

Code Implementation

In your code, the AddItemsToReport method can be modified to use a TVP as follows:

public void AddItemsToReport(string connStr, int Id, List<int> itemList)
{
    Database db = DatabaseFactory.CreateDatabase(connStr);

    string sqlCommand = "AddItemsToReport";
    DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

    // Create TVP DataTable
    DataTable itemTable = new DataTable("Items");
    itemTable.Columns.Add("ItemId", typeof(int));

    // Fill TVP DataTable
    foreach (int item in itemList)
        itemTable.Rows.Add(item);

    // Add TVP parameter
    db.AddParameter(dbCommand, "Items", DbType.Object, itemTable);
    db.ExecuteNonQuery(dbCommand);
}

In the stored procedure:

INSERT INTO ReportItem (ReportId, ItemId)
SELECT  ReportId,
          ItemId
FROM     @Items

Benefits

TVPs offer several benefits over the previous approach:

• Simplifies and streamlines code
• Improves performance by reducing parameter insertion overhead
• Enhances data integrity by ensuring the items in the list are of the correct type

Compatibility Considerations

Note that TVP support in SQL Server 2008 is limited to that version and later. If dealing with earlier versions of SQL Server, alternative solutions, such as XML parameters or user-defined functions, may need to be considered.

The above is the detailed content of How Can Table-Valued Parameters Improve Passing Lists to SQL Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!