How Can Table-Valued Parameters Improve Efficiency When Passing Multiple Items to SQL Stored Procedures?

Taming Stored Procedures with Table-Valued Parameters

Problem:
Efficiently transferring multiple items to a single database record remains a persistent challenge. For instance, assigning multiple items to a report requires updating the ReportItems table with multiple records. Conventional approaches involve complex code and an auxiliary function to process the item list as a string.

Unveiling a Better Solution

With the advent of SQL Server 2008, "Table-valued parameters" introduce a groundbreaking solution to this conundrum.

The Magic of TVPs:
Table-valued parameters allow you to pass a table-like structure as a parameter to a stored procedure. In our case, we can define a TVP to accept a table of integer item IDs.

Revised Code:
The revised code becomes much more concise:

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

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

    // Convert the list to a DataTable (lightweight)
    DataTable table = itemList.ToDataTable();

    // Create the TVP parameter
    db.AddParameter(dbCommand, "Items", table);

    // Execute the stored procedure
    db.ExecuteNonQuery(dbCommand);
}

Enhanced Stored Procedure:
The stored procedure simplifies further:

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

Conclusion:
Table-valued parameters offer an elegant and efficient mechanism for transferring multiple items to SQL stored procedures. By eliminating the need for complex string manipulation and auxiliary functions, TVPs streamline the development process and enhance code readability.

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