Table-Valued Parameters (TVP) in SQL Server 2008 and Later

In this article, we will be discussing about Table-Valued Parameters (TVPs) in SQL Server 2008 and later.

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

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.