Table Valued Parameters in SQL 2008

Introduction

Microsoft SQL Server 2008 has been upgraded to accept table values as parameters for the Stored procedures(SPs). Once a table valued parameter(TVP) is passed as parameter to SP, it can be used like any table but we cannot modify the parameter as it is readonly. Table valued parameters can be used to have multiple values as parameters for the SP, which was done earlier using one of the following ways:

1. Using a series of multiple parameters
2. Using delimited string or XML documents and pass those text values to SP
3. Creating a series of individual SQL statements that affect multiple rows.

Using the Code

TVPs are defined using  the user-defined table types. The table type is used to describe the structure of the TVP. We can see that the TVPs are strongly typed.

Using a Table value parameter:

1. Create a Table Type and define the table structure:

      Create type CustomerType as Table
      (
        CustomerID INT
      );
      GO

2. Declare a SP that has a parameter of table type:

   CREATE PROCEDURE GetAllCustomer @CustomerIDs_TVP CustomerType READONLY
   AS
   SELECT CustomerID
       ,CustomerName
   FROM Customer
   INNER JOIN @CustomerIDs_TVP ON Customer.CustomerID = @CustomerIDs_TVP.CustomerID
   GO

3. Using the TVP as parameter with the SP:

   DECLARE @CustomerIDs_TVP AS CustomerType
   INSERT INTO @CustomerIDs_TVP (CustomerID) VALUES (1),(2),(3),(4),(5)
   EXEC GetAllCustomer @CustomerIDs_TVP;
   GO

We can see the output of the above executed query is all the customers from Customer table having customerIDs 1, 2, 3, 4, 5.

Using TVP in C# .NET

DataTable tableCustomerIds = new DataTable();
tableCustomerIds.Columns.Add("CustomerID", typeof(int));
tableCustomerIds.Rows.Add(1);
tableCustomerIds.Rows.Add(2);
tableCustomerIds.Rows.Add(3);
tableCustomerIds.Rows.Add(4);
tableCustomerIds.Rows.Add(5);

using (SqlConnection conn = new SqlConnection("Server=localhost;Database=MyDB;Trusted_Connection=True;"))
{
    SqlCommand cmd = new SqlCommand("GetAllCustomer", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter sqlParam = cmd.Parameters.AddWithValue("@CustomerIDs_TVP", tableCustomerIds);
    cmd.CommandTimeout = 0;
    sqlParam.SqlDbType = SqlDbType.Structured; //tells ADO.NET we are passing TVP     
    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
    DataSet sptable = new DataSet();
    adapter.Fill(sptable);
}

This was all about the table values parameters in SQL 2008 server. Please let me know your thoughts about the tip.