Table Valued Parameters in SQL Server 2008

One great new feature in SQL Server 2008 is Table Valued Parameters (TVP). They allow you to easily solve a common and difficult scenario where you want to insert or update multiple rows in a table without making multiple database calls or using somewhat nasty methods like large IN clauses.

Here is an example. You create a custom type as a Table, then you create a stored procedure that uses that type as an input parameter. Then you declare a local variable as that custom table type, load it with data, and finally call the stored procedure, passing in the loaded table type to do multiple INSERTS or UPDATES, etc.

Server Side Code

CREATE TYPE CustomerListType AS Table (id int, namenvarchar(128), createdate datetime);
CREATE PROCEDURE AddCustomer(@CustomerList CustomerListType READONLY)
As
INSERT INTO dbo.Customers
SELECT * FROM @CustomerList

Client Code

DECLARE @tvp1 CustomerListType;
INSERT INTO @tvp1 values(1, N'Microsoft', '2007-06-04');
INSERT INTO @tvp1 select id, name, createdate from dbo.OldCustomers;
EXEC AddCustomer @tvp1;

Technorati Tags: ,
This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s