Table Valued Parameters in SQL Server 2008

As developers, there is always a need to pass data structures in one form or another in any programming language. TSQL is no exemption. This has been addressed by some workarounds as OPENXML in SQL Server 2000 where you can pass data around as VARCHAR data type. in SQL Server 2005, you have the XML data type where you can store data in XML form in a database column and use XPath with XQuery to manipulate the data. Both of which can, indeed, help pass data structures but still needs a lot of work to make it happen. Another approach is to create a temporary table on demand, populate the table before actually using the data in a stored procedure or function. SQL Server 2008 offers you the capabilities of passing a table as a parameter in a stored procedure and a function. Imagine populating a table on a client application and pass the table as a parameter to the backend database at one shot, thereby eliminating a lot of roundtrips, not to mention a couple of lines of codes. To understand how it works, let's have a look at a sample code. First, I'll create a very simple table named TableTVP1 which will be our target table.

--create sample table
CREATE TABLE [dbo].[TableTVP1]
(rowID int)
GO

The first step in understanding the concept of a table-valued parameter is to create a table-type; a new parameter which is of type table.

--Create TABLE type
CREATE TYPE tvp_TableTVP1 AS TABLE
(rowID int)
GO

The good thing about creating a table-type is that you can re-use it similar to re-using a user-defined data type. This means, you can use it to access any table with similar structure using the table-type(imagine having tables like Employees, Customers, Partners, etc. having similar table structures). After we have created the table-type, we can now use it it a stored procedure or function. The code below illustrates the use of the table-type we've just created

--Setup stored procedure to accept the new TABLE type
CREATE PROCEDURE usp_tvpInsert (@tableparam tvp_TableTVP1 READONLY)
AS
INSERT TableTVP1
SELECT * FROM @tableparam
GO

Notice how we used the table-type tvp_Table1TVP1 as a type to define the parameter @tableparam. It's just like any parameter you pass to a stored procedure or function except that it is defined as a table. The READONLY attribbute passed simply tells us that a table-type passed as a parameter cannot be modified and no DML operations are permitted unlike when dealing with temporary tables. This means you need to do the data manipulation outside of this stored procedure or function before you can pass the table-type. Now that we have both the table-type and the stored procedure, let's see how it works.

--Use the stored procedure
DECLARE @tableparam tvp_TableTVP1
DECLARE @i SMALLINT
SET @i = 1

WHILE (@i <=1000)
BEGIN
INSERT @tableparam VALUES (@i)
SET @i = @i + 1
END

EXEC usp_tvpInsert @tableParam

The code simply inserts records from 1 to 1000 on a table-type named @tableparam and passed it to the stored procedure. Imagine doing this in your client application and passing it one-time as a parameter in a stored procedure. It reduces the number of server roundtrips and improves response time aside from benefitting from being able to pass a table as a parameter. Check out my SQL Server 2008 videos at BlogCastRepository.com where a .NET application is also available for download as a sample that uses TVP