How Can I Pass an Array of Parameters to a SQL Server Stored Procedure Efficiently?

Use table-valued parameters (TVP) to efficiently pass parameter arrays to stored procedures

Passing an array of parameters is a very common task when using stored procedures. This article will explore how to pass an array of identifiers (IDs) to a stored procedure to perform a specific operation.

Problem Description

We need to pass an array of IDs to a stored procedure that will delete all rows in the table except those that match the IDs in the array.

Solution using table-valued parameters

In SQL Server 2008 and later, table-valued parameters (TVPs) provide an efficient way to pass arrays to stored procedures. Here's how to implement this using TVP:

1. Create TVP

Create a TVP to represent the ID array:

<code class="language-sql">CREATE TYPE T1Ids AS Table (
    t1Id INT
);</code>

2. Create stored procedure

Create a stored procedure using TVP:

<code class="language-sql">CREATE PROCEDURE dbo.FindMatchingRowsInTable1( @Table1Ids AS T1Ids READONLY )
AS
BEGIN
    -- 您的存储过程逻辑在此处
END</code>

3. Prepare TVP data

In your application, prepare the TVP data into a DataTable:

<code class="language-csharp">DataTable t1Ids = new DataTable();
t1Ids.Columns.Add("t1Id", typeof(int));
foreach (int id in listOfIdsToFind)
{
    t1Ids.Rows.Add(id);
}</code>

4. Pass TVP to stored procedure

When calling the stored procedure, pass TVP as a parameter:

<code class="language-csharp">SqlParameter sqlParameter = new SqlParameter("Table1Ids", t1Ids);
findMatchingRowsInTable1.Parameters.Add(sqlParameter);</code>

5. Execute stored procedure

Execute the stored procedure and process the results.

Advantages of using TVP

• Type-safe parameter passing
• Improved performance compared to serializing and deserializing data
• Readable and easy-to-use syntax

This approach simplifies the task of performing complex operations on a data set by allowing you to easily pass an array of parameters to a stored procedure.

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