Blog

Table valued parameters (TVPs) are nothing new – they were introduced in SQL Server 2008. TVPs are a great way to get data into SQL Server, and yet very few developers use them.

Getting Started with TVPs

In order to get started, we need to create a user defined type. This type is necessary so we have a well defined interface between SQL Server and the outside world – you can just pass a bunch of junk in as a parameter to SQL Server… well, you could, but that’s called XML.

Let’s create that user defined type:

CREATE TYPE dbo.SalesPersonTerritory AS TABLE
(
    SalesPersonID INT,
    TerritoryID INT
);

Now we can create variables using the dbo.SalesPersonTerritory type whenever we want. It’s just this easy:

DECLARE @spt SalesPersonTerritory;
SELECT * FROM @spt;

Using Table Valued Parameters With Stored Procedures

Having a special table type is really convenient, but it doesn’t help if you can’t use it, right? Let’s assume that you’re calling a stored procedure and you’re sick of joining lists of strings on the client and then splitting them apart on the server. What you really need is the ability to pass a fully fledged table across the wire and into SQL Server.

This is relatively easy to accomplish. From C#, the code would look something like this:

string cnString = @"data source=.;initial catalog=TVPTester;user id=tvptester;password=tvptester;";

using (var connection = new SqlConnection(cnString))
{
    connection.Open();
    using (SqlCommand cmd = new SqlCommand("SELECT * FROM @tvp;", connection))
    {
        var pList = new SqlParameter("@tvp", SqlDbType.Structured);
        pList.TypeName = "dbo.SalesPersonTerritory";
        pList.Value = SalesPersonTerritoryTable();

        cmd.Parameters.Add(pList);

        using (var dr = cmd.ExecuteReader())
        {
            while (dr.Read())
                Console.WriteLine(dr["Item"].ToString());
        }
    }
}

That’s really all there is to it.

C# Tips for Table Valued Parameters

There are a few other things that developers can do to make their life easier when working with table valued parameters. A DataTable, just like a table in SQL Server, should have types declared for all columns. While it’s easy enough for developers to create these tables on the fly, that won’t cut it in production code – boiler plate code means that people can make mistakes.

To make life easier, developers can create methods in their application to make it easier to work with table valued parameters. We can create a special chunk of code that will make it easy for developers to instantiate and use a DataTable that matches up with the table valued parameter.

static DataTable SalesPersonTerritoryTable()
{
    var dt = new DataTable();
    dt.Columns.Add("SalesPersonID", typeof(int));
    dt.Columns.Add("TerritoryID", typeof(int));
    return dt;
}

Seasoned developers will even create methods that let them drop a list of objects straight into an appropriate DataTable. There are many different ways to build convenience methods into code and make it easy for development teams to work with TVPs.

For simple data type matching, this works well. If you’re dealing with more complex data types, you’ll want to check out SQL-CLR Type Mapping to make sure you get the right data type. You’ll notice that some datatypes (varchar for instance) have no direct corollary in the .NET Framework. Sometimes you just have to lose some fidelity – make wise decisions, it gets crazy out there.

These same techniques can be used with a string of ad hoc SQL, too. TVPs aren’t limited to stored procedures, they can be used anywhere that you are executing parameterized code.

Gotchas of TVPs

There are two big gotchas with TVPs.

First: the table variable that comes in as a table valued parameter cannot be changed. You’re stuck with whatever values show up. No inserts, updates, or deletes can be applied.

Second: table valued parameters are still table variables – they get terrible cardinality estimates.

We can get around both of these problems with the same technique – copy the contents of the TVP into a temp table. Although it adds an extra step to using the TVP, I’ve found that copying the contents of the TVP to a temporary table lead to better execution plans and much less confusion during development and troubleshooting.

Summary – Using Table Valued Parameters isn’t Hard.

TVPs aren’t difficult to use. They’re just different and require a different mindset. By using TVPs, developers can pass many rows into a stored procedure and create far more complex logic and behavior that is possible using only single parameter values.

↑ Back to top
  1. Hi Jeremiah,
    The code you have provided won’t work nor compile. Here is an adjusted version that I was able to execute.

    string cnString = @”data source=.;initial catalog=TVPTester;user id=tvptester;password=tvptester;”;
    using (var connection = new SqlConnection(cnString))
    {
    connection.Open();
    using (SqlCommand cmd = new SqlCommand(“SELECT * FROM @tvp;”, connection))
    {

    var pList = new SqlParameter(“@tvp”, SqlDbType.Structured);
    pList.TypeName = “dbo.SalesPersonTerritory”;
    pList.Value = SalesPersonTerritoryTable();

    cmd.Parameters.Add(pList);

    using (var dr = cmd.ExecuteReader())
    {
    while (dr.Read())
    Console.WriteLine(dr["Item"].ToString());
    }
    }
    }

    Thanks for the post. I had no idea you could do this.

  2. Pingback: My links of the week – February 9, 2014 | R4

  3. How would this work with Report Server?

  4. Pingback: (SFTW) SQL Server Links 14/02/14 • John Sansom

  5. Good article. Hopefully it will give me insight into determining what is causing the following error when using a table valued param. I’m thinking putting the data into a temp table is a good start in troubleshooting.

    “The data for table-valued parameter XXXXX doesn’t conform to the table type of the parameter.”

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php