SQL Server 2008: Table Value Parameters (TVP)

In this post, I am going to give an overview of the new Table Value Parameters feature coming in SQL Server 2008. Table Value Parameters allow you to pass user defined tables between queries, but also between client and server. With them being tables, you can do various different things with them, for example querying, joining or inserting values just like you would with a normal table. Now, instead of having the query take a long list of parameters they can simply just take a TVP as a parameter.

To create a TVP, you have to define a user defined type and the columns which the TVP will hold. Here we create a type of Customer which can hold an id, their name and postcode.

CREATE TYPE Customer AS TABLE (id int, CustomerName nvarchar(50), postcode nvarchar(50))

Then we create a dummy table to store the information.

CREATE TABLE Customers (id int, CustomerName nvarchar(50))
GO

CREATE TABLE CustomerPostCodes(id int, postcode nvarchar(50))
GO

We now create a procedure which takes a single parameter, which is a TVP. Based on this, we can insert the data into two separate tables, however from the outside its just a single object and a single stored procedure being called.

CREATE Procedure AddCustomers(@customer Customer READONLY)
AS

INSERT INTO Customers
SELECT id, CustomerName FROM @customer

INSERT INTO CustomerPostCodes
SELECT id, postcode FROM @customer

GO

One important point when using TVP’s as parameters is that they must have the READONLY attribute. Another important point, the TVPs are actually temp tables stored on the server in tempdb. As you can see, the stored procedure inserts the records from Customer, Customer could actually contain multiple customers into the related table by querying the table.

In order to use a TVP, we need to declare it like we would with a variable.

DECLARE @myNewCustomer Customer;

We can then use the table like we would any other table.
INSERT INTO @myNewCustomer VALUES (1, ‘Harry’, ‘NEW’)

Finally, we can execute the stored procedure and pass the table as a parameter.
EXEC AddCustomers @myNewCustomer
GO

If we query the two tables, then it will contain the following records.

id CustomerName
———– ——————–
1 Harry

id postcode
———– ————
1 NEW

TVP will solve a lot of problems and allow for a much more streamlined experience. TVP allows for joins, queries etc, and for inserting small number of records (<1000 is recommended). for larger inserts (>1000) – look at the Bulk insert functionality.

But, they would be a bit limited if you could use them only within SQL itself, so there is full support within ADO.net 3.0, using SqlDbType.Structured type. The parameters which can be passed in from ADO.net are DataTables, IEnumerable and DbDataReader. There is also support for ODBC, SSMS, and within Replication.

For example, adding a datatable as a parameter to the stored procedure from C# would have been something like:

DataTable dt = new DataTable();
dt.Columns.Add….
dt.Rows.Add…..
SqlCommand cmd = new SqlCommand(“AddCustomer”, sqlConn);
cmd.Parameters.AddWithValue(“@Customer”, dt);
cmd.ExecuteNonQuery();

Now that’s cool. Hope you have found this useful.

Links

Download my SQL sample here

Related Posts

SQL Server 2008- Change Data Capture (CDC)

SQL Server 2008- Declarative Management

SQL Server 2008- SQL MERGE Statement

Katmai June CTP