Table-Valued Parameters (TVP) in SQL Server 2008

Support of Table-Valued Parameters is another great new feature in SQL Server 2008.

I am sure that all of us had to make a stored procedure or function call in SQL Server many times. In the cases where many values should be passed as parameters it was a little bit difficult as we had to use some really long lines of code. Not any more with Table-Valued Parameters 🙂

The usual procedure for using TVPs is the following:

1. Create a user-defined table type that can be passed as a TVP to a function or stored procedure

Code Example:
–Create a table type
CREATE TYPE employee AS TABLE
(ID int,
NAME varchar(50))
GO
2. Create a stored procedure or function that uses a TVP

Code Example:
–Create a stored procedure that takes as a parameter a TVP
CREATE PROCEDURE showTVPValues(@TVParameter employee READONLY)
AS
SET NOCOUNT ON
SELECT * FROM @TVParameter
GO

3. Declare the table type variable

Code Example:
–Declare a variable that references the user-defined table type
DECLARE @TVP_Param AS employee;

4. Populate the table type variable with data

Code Example:
–Add data to the table variable
INSERT INTO @TVP_Param (ID, NAME)
SELECT [ID],[NAME]
FROM [table_name].[column_name];

5. Pass the table type variable as a parameter to the stored procedure

Code Example:
–Pass the table variable to the stored procedure
EXEC showTVPValues @TVP_Param;
GO

With this way you can pass tabular data to a function or stored procedure as a single parameter.