Click here to Skip to main content
15,119,363 members
Articles / Database Development / SQL Server / SQL Server 2008R2
Tip/Trick
Posted 5 Mar 2015

Stats

13K views
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.

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:
    SQL
       Create type CustomerType as Table
       (
         CustomerID INT
       );
       GO
  2. Declare a SP that has a parameter of table type:
    SQL
    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:
    SQL
    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

C#
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.

License

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

Share

About the Author

DotNetForAll
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
MemberL 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.