“All together now…”

One of the features I have missed most in SQL Server is the ability to pass multiple "rows" of data to a stored procedure. This is especially useful in the canonical Order/Order Line Items scenario where you want to insert a header row and multiple child rows into their respective tables in 1 transaction. It applies, however, anytime you want to pass lots of rows/sets/arrays of data to the database at once.

You can of course support this scenario today using transactions from the client with multiple SP calls, but the ideal scenario for performance and simplicity is to be able to achieve this with a single call (and a single round trip) to the database.

SQL Server 2008 introduces this capability in the form of a feature called Table-Valued Parameters. It is available to check out in the recently release June CTP (download it and give it a try!). It is most compelling when you see how it impacts the client programming experience, but the ADO .NET support for this feature won't surface until Orcas Beta 2. For now, we will explore the server side of this feature.

One of the key concepts to learn about TVP's is that the tables that you pass as parameters are strongly typed database objects. You define these ahead of time (much as any other user defined type). In this example I need a table type to hold a list of order line items, as follows:

CREATE
TYPE OrderItemList AS
TABLE(

    OrderItemID int,

    OrderID int,

    ItemID int,

    ItemQuantity int,

    ItemPrice money

)

In this case, to keep things simple, this table "type" matches the definition for an actual table where I will store these items in the database.

Next I use this new type as a parameter to a stored procedure to insert orders:

CREATE
PROCEDURE [dbo].[InsertOrder]

 

    @CustomerID int,

    @OrderDate datetime,

@ItemList OrderItemList READONLY

 

AS

SET
NOCOUNT
ON

 

    INSERT
INTO Orders (CustomerID, OrderDate)
values
(@CustomerID, @OrderDate)

 

    DECLARE @OrderID int

 

    SET @OrderID =
SCOPE_IDENTITY()

 

    INSERT
INTO OrderItems

(OrderID

,ItemID

,ItemQuantity

,ItemPrice)

SELECT @OrderID, ItemID, ItemQuantity, ItemPrice

FROM @ItemList;

As you can see, this procedure uses the TVP just as if it were a normal database table to source data for inserts through the INSERT
INTO … SELECT… syntax.

In upcoming version of ADO .NET, you will call this SP just as you call others except you will pass a "table" like value to the TVP parameter (probably with something like a DataTable). Here is what it looks like to call from within T-SQL.

First we need to declare a variable of our specific table type and fill it with a few rows. Notice that un T-SQL, you treat the table-type variable like other tables:

DECLARE @ItemList

AS OrderItemList;

 

INSERT
INTO @ItemList (ItemID, ItemQuantity, ItemPrice)

VALUES
(1, 1, 1.00);

INSERT
INTO @ItemList (ItemID, ItemQuantity, ItemPrice)

VALUES
(2, 2, 2.00);

INSERT
INTO @ItemList (ItemID, ItemQuantity, ItemPrice)

VALUES
(3, 3, 3.00);

Then you simple pass this variable as you would any other into the stored procedure…

exec InsertOrder 100,
'4-Jul-2007', @ItemList;

…which insert the rows into the tables…

OrderID CustomerID OrderDate

----------- ----------- -----------------------

1 100 2007-07-04 00:00:00.000

 

 

 

OrderItemID OrderID ItemID ItemQuantity ItemPrice

----------- ----------- ----------- ------------ ---------------------

1 1 1 1 1.00

2 1 2 2 2.00

3 1 3 3 3.00

 

I hope you are as excited about this feature as I am. It has the potential to simplify a lot of database and data access code as well as enhance performance in many scenarios. We'll look at it again from the client perspective once Visual Studio "Orcas", Beta 2 is available.