How to Efficiently Pass Arrays to Stored Procedures Using Table-Valued Parameters (TVPs)?

Using Table-Valued Parameters (TVPs) to Pass Arrays to Stored Procedures

Database applications frequently require passing arrays or lists of values to stored procedures. For SQL Server 2008 and later, Table-Valued Parameters (TVPs) offer the most efficient solution.

Creating a Stored Procedure with a TVP

This involves first defining the TVP type, then creating the stored procedure:

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

CREATE PROCEDURE dbo.FindMatchingRowsInTable1 (@Table1Ids AS T1Ids READONLY)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Table1.t1Id 
    FROM dbo.Table1 AS Table1
    JOIN @Table1Ids AS paramTable1Ids ON Table1.t1Id = paramTable1Ids.t1Id;
END</code>

Calling the Stored Procedure

Within your application (example using C#), prepare the data as a DataTable, establish the database connection, and create the command object:

<code class="language-csharp">DataTable t1Ids = new DataTable();
t1Ids.Columns.Add("t1Id", typeof(int));

int[] listOfIdsToFind = new[] { 1, 5, 9 };
foreach (int id in listOfIdsToFind)
{
    t1Ids.Rows.Add(id);
}

SqlConnection testbedConnection = new SqlConnection(
    @"Data Source=.\SQLExpress;Initial Catalog=TestbedDb;Persist Security Info=True;User ID=testbedUser;Password=letmein12;Connect Timeout=5");

SqlCommand findMatchingRowsInTable1 = new SqlCommand("dbo.FindMatchingRowsInTable1", testbedConnection);
findMatchingRowsInTable1.CommandType = CommandType.StoredProcedure;

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

Finally, execute the stored procedure and handle the results:

<code class="language-csharp">SqlDataReader sqlDataReader = findMatchingRowsInTable1.ExecuteReader();

while (sqlDataReader.Read())
{
    Console.WriteLine("Matching t1ID: {0}", sqlDataReader["t1Id"]);
}</code>

TVPs offer a type-safe and efficient method for passing arrays or lists to stored procedures, improving database application performance and maintainability.

The above is the detailed content of How to Efficiently Pass Arrays to Stored Procedures Using Table-Valued Parameters (TVPs)?. For more information, please follow other related articles on the PHP Chinese website!