Click here to Skip to main content
15,513,143 members
Articles / Database Development / SQL Server / SQL Server 2008R2
Posted 5 Mar 2015


3 bookmarked

Table Valued Parameters in SQL 2008

Rate me:
Please Sign up or sign in to vote.
4.00/5 (4 votes)
5 Mar 2015CPOL1 min read
The tip describes how the Table Valued Parameters(TVP) are used in SQl server and how they can be utilized using C# code.


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
  2. Declare a SP that has a parameter of table type:
    CREATE PROCEDURE GetAllCustomer @CustomerIDs_TVP CustomerType READONLY
    SELECT CustomerID
    FROM Customer
    INNER JOIN @CustomerIDs_TVP ON Customer.CustomerID = @CustomerIDs_TVP.CustomerID
  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;

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));

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();

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


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Software Developer (Senior)
India India
I have 8 years of experience of mostly developing the .NET windows as well as web applications. Passionate about learning and sharing.

Visit my personal blog to know more about the .NET technology and increase your knowledge about C# and .NET. You can always follow me on twitter @dotnetforall.

Comments and Discussions

GeneralMy vote of 4 Pin
L Hills11-Mar-15 8:08
L Hills11-Mar-15 8:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.