Passing Table-Valued Parameter Lists to SQL Stored Procedures

The scenario of populating a database table with multiple items associated with a specific record is a common challenge in data manipulation. While traditional approaches involve iteratively passing parameter values or using string manipulation, a more efficient solution is available.

Solution: Table-Valued Parameters in SQL Server 2008

SQL Server 2008 introduced table-valued parameters, which allow passing a collection of structured data as a parameter to a stored procedure. This eliminates the need for complex string manipulation or iterative parameter handling.

In the provided code, the "AddItemsToReport" stored procedure can be updated to accept a table-valued parameter:

ALTER PROCEDURE AddItemsToReport
(
  @ReportId int,
  @Items AS ItemList -- User-defined table type
)
AS
BEGIN
  INSERT INTO ReportItem (ReportId, ItemId)
  SELECT  @ReportId,
            Id
  FROM    @Items
END

Custom Table Type for Item List

To create a custom table type for the item list, execute the following SQL statement:

CREATE TYPE ItemList AS TABLE
(
  Id int
)

Passing Table-Valued Parameter from C#

In your C# code, create an instance of the custom table type:

var itemList = new ItemList();
itemList.Rows.Add(1);
itemList.Rows.Add(2);
itemList.Rows.Add(3);

Then, pass the table-valued parameter to the stored procedure:

dbCommand.AddInParameter(dbCommand, "Items", DbType.Object, itemList);

Benefits of Table-Valued Parameters

• Improved performance by avoiding multiple round trips to the database.
• Simplified code by eliminating the need for string manipulation or iteration.
• Enhanced security by strongly typing the input data.