How to Efficiently Pass a List of Integers to a SQL Stored Procedure?

Passing List to SQL Stored Procedure

SQL stored procedures are a convenient way to group database operations, ensuring data integrity and simplifying the development process. However, passing lists of values to stored procedures can be challenging.

Current Approach

Traditionally, developers have resorted to converting lists into concatenated strings within the code. For example, the code snippet provided iterates through a list of integers, concatenating them with a delimiter (e.g., "~") to create a single string. This string is then passed as a parameter to the stored procedure, which uses a user-defined function to split it into a table of integers.

While this approach works, it can be inefficient and error-prone.

Table-Valued Parameters

SQL Server 2008 introduced a feature called table-valued parameters, which provides a more elegant solution to this problem. Table-valued parameters allow you to pass a table as a parameter to a stored procedure. This eliminates the need for string concatenation and user-defined functions.

Updated Code

To utilize table-valued parameters, the code and stored procedure would need to be updated:

Code

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

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

    DataTable itemsTable = ToDataTable(itemList);

    // Add parameters
    db.AddInParameter(dbCommand, "ReportId", DbType.Int32, Id);
    db.AddTableParameter(dbCommand, "Items", itemsTable);
    db.ExecuteNonQuery(dbCommand);
}

private DataTable ToDataTable<T>(List<T> list)
{
    DataTable table = new DataTable();
    table.Columns.Add("Id", typeof(T));

    foreach (var item in list)
        table.Rows.Add(item);

    return table;
}

Stored Procedure

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

In this updated code, the list of integers is converted into a DataTable and passed as a table-valued parameter. The stored procedure no longer needs a custom function to extract the values.

This approach provides several benefits:

• Increased efficiency
• Reduced code complexity
• Improved code readability
• Better support for large datasets

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