Table valued parameters in SQL Server 2008

The June CTP of SQL Server 2008 contains support for table-valued parameters. Here's a usage scenario for these that has been around for a while.

Imagine you are running an online store and deal with (among other data) orders and order detail lines. You'd like to have a stored procedure that can add an entire order in one server round trip, regardless of the number of items that I order (that is, 1 order header and 1->n order detail lines). You'd even settle for two round trips, one for the order header, one for the details. Before SQL Server 2008, there is no built-in mechanism that supports this. In the past, I've seen some pretty interesting workarounds, such as:

1. Compose an arbitrarily large SQL batch as a single "command text" by using string concatenation on the client/middle-tier. All SQL Server database APIs support one (and only one) batch per Command. ADO classic did something like this when you inserted/updated/deleted multiple rows in a disconnected Recordset and called for a "batch update".

2. Create a stored procedure with some "static" parameters and an arbitrarily large number of repeating parameters, most of which will always be NULL. The limit to the number of parameters in a stored procedure is 2100. Both this method and the previous one make for some pretty hideous-looking code.

3. Use multiple parameter sets. OLE DB does support multiple parameter sets and some databases can optimize inserts that use multiple parameter sets. The SQL Server providers, at least last time I looked with SQL Profiler, turn multiple parameter sets into multiple calls, that is, one round trip per parameter set. That's not what I wanted.
 
SQL Server 2008's solution to this age-old problem is table-valued parameters (TVPs, for short). You start using a TVP by creating a custom type, using the CREATE TYPE statement, like this:

CREATE TYPE lineitem_type (line_number INT, order_id INT, product INT, quantity INT);

Information about these table types appear in sys.types and also in a new metadata view, sys.table_types. Once you've created such a table type, you can use it in T-SQL like this:

CREATE PROC new_order (@order_id INT, @line_items lineitem_type)
AS
— silly table names used for clarity
INSERT INTO orders_table VALUES (@order_id … ) ;
INSERT INTO line_items_table
  SELECT * FROM @line_items;

One round trip. Compact, clean code. Nice.

4 thoughts on “Table valued parameters in SQL Server 2008

  1. Another "workaround" is to send the order, and order detail, information to the stored procedure as XML. This method worked fine with SQL Server 2000, and works even better with SQL Server 2005. In the Ajax world, it is very likely that the order information will already exist as XML. Why not just pass that XML to the database, and deal with it there?

  2. Hi Scott,

    Sending the XML means that you have to decompose it inside of a stored procedure using OpenXML or the XML nodes method (XML decomposition code in every stored procedure). Although these both work, neither should be as performant (either in memory consumption or in CPU utilitization) as SELECT INTO. We’ll have to wait for perf tests to confirm this. But you’re right, I’d forgotten about that one in my list of alternatives.

    Cheers,
    Bob

  3. Any idea what the client side (ie. ADO) code would look like for this. In other words, how would a c# .NET client send a table-valued parameter to a stored procedure?

    Scott

  4. Hi,
    we use another strategy. The client program inserts the details in an auxiliary table (aux_order_details) with a defined key for the records filled in that session (for example, user id, machine name…).
    When we call the SP, it "knows" that it must copy the values from that table:


    create procedure insert_order @givenuserkey
    as

    insert into Order_details
    select …
    from aux_order_details
    where key = @givenuserkey

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.