Table Valued Parameters in SQL 2008

Miscrosoft SQL Server 2008 has been upgraded to accept table values as parameters for Stored Procedures (SPs). Once a Table Valued Parameter (TVP) is passed as a parameter to a SP it can be used like any table but we cannot modify the parameter since it is readonly. Table Valued Parameters can be used to have multiple values as parameters for the SP that reviously was done 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.

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 TVP 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 READON
   LY
   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 preceding executed query is all the customers from Customer table having customerIDs 1, 2, 3, 4 and 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 = newSqlConnection("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 Table Valued Parameters in SQL Server 2008. Please let me know your thoughts about the article.