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.”

    • Jeremiah,

      I’ve tried to implement the above, though the SQL portion of things won’t cooperate. Using your “type” name as an example, I’ve tried to create the following stored procedure:

      CREATE PROCEDURE #TEST
      (
      @LIST as dbo.SalesPersonTerritory
      )
      AS
      BEGIN
      SELECT * FROM @LIST
      END

      though I get the following error:

      Msg 2715, Level 16, State 3, Procedure #TEST, Line 2
      Column, parameter, or variable #1: Cannot find data type dbo.SalesPersonTerritory.
      Parameter or variable ‘@LIST’ has an invalid data type.
      Msg 1087, Level 16, State 1, Procedure #TEST, Line 8
      Must declare the table variable “@LIST”.

      I ran the create script, and the datatype does appear when I query sys.types…

      FYI, when I try to use it as a declared variable it works fine… it’s only in the stored procedure that I’m getting errors.

      What am I doing wrong?

      Eli

      • Jeremiah,

        I discovered that it would buy it if I did it as a regular stored procedure, though the temp portion is what threw it off… why would a temp proc not recognize a user defined type?

        Eli

        • I suspect it’s because the temporary stored procedure is created within tempdb. That is, after all, where temp tables live. You’d have to three part name the UDT since UDTs are local to a specific database.

      • Assuming you have actually created the type dbo.SalesPersonTerritory correctly, you left out the READONLY keyword in the parameter list of the stored proc. This is mandatory. It should look like

        CREATE PROCEDURE #TEST
        (
        @LIST dbo.SalesPersonTerritory READONLY
        )
        AS
        BEGIN
        SELECT * FROM @LIST
        END

  6. Thanks for the article & user tips especially the C# snippet.

    One thing I would add about User-defined table types as TVPs is that they implicitly default to an empty table. When used as a parameter for a stored procedure, attempting to set them = NULL as a default makes no sense.

    It is then possible to test if the TVP has been passed in empty (or not at all) by checking IF EXISTS any rows.

  7. I’ve been using TVPs for a little while now, and one of the really neat tricks I’ve found that isn’t well-known is the use of the SqlDataAdapter.FillSchema method (http://msdn.microsoft.com/en-us/library/152bda9x(v=vs.110).aspx) to add correctly-typed columns for a table (or type) to a DataTable. By using code like the example below, the .NET framework will populate a DataTable with the set of columns for the table type including constraints for not null and string length. The constraints are particularly useful for catching individual rows that might cause entire bulk inserts or updates to fail since DataTable.Rows.Add will throw an exception if such constraints are violated. To keep from needing an extra round trip to the server to retrieve the schema for every DataTable, a DataTable with the schema and no entries can be cached and DataTable.Clone can be used to make new tables as necessary.

    CREATE TYPE MyTableType AS TABLE
    (
    Col1 INT NOT NULL,
    Col2 INT NOT NULL,
    Col3 VARCHAR(32) NOT NULL
    )

    ———————————————————————————————————-

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

    using (SqlConnection connection = new SqlConnection(cnString))
    using (DataTable dataTable = GetDataTableForType(connection, “MyTableType”))
    {
    DataRow dataRow = dataTable.NewRow();
    dataRow[“Col1″] = 15;
    dataRow[“Col2″] = 10;
    dataRow[“Col3″] = “MyString”;

    // if any schema constraints are not met, an exception will be thrown out of this call
    dataTable.Rows.Add(dataRow);

    // push the data using a SqlCommand and a Structured parameter
    }

    ———————————————————————————————————-

    const string getSchemaCommand = “DECLARE @schemaTable [{0}]; SELECT * FROM @schemaTable”;
    static readonly Dictionary schemaTableCache = new Dictionary(StringComparer.InvariantCultureIgnoreCase);

    static DataTable GetDataTableForType(SqlConnection connection, string typeName)
    {
    DataTable schemaTable;

    // check to see if the table schema is already cached
    if (!schemaTableCache.TryGetValue(typeName, out schemaTable))
    {
    schemaTable = new DataTable(typeName);

    // retrieve the table schema from the database; the adapter will open the connection if it is not
    // already open and close it after completing if it was initially closed
    using (SqlCommand getTableCommand = new SqlCommand(string.Format(getSchemaCommand, typeName), connection))
    using (SqlDataAdapter adapter = new SqlDataAdapter(getTableCommand))
    {
    // Source = don’t apply adapter transformations (none are defined on this adapter)
    adapter.FillSchema(schemaTable, SchemaType.Source);
    }

    // cache the schema
    schemaTableCache[typeName] = schemaTable;
    }

    // return a clone of the schema reference table
    return schemaTable.Clone();
    }

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