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 Procedure for Creating Table-Valued Parameters in SQL Server

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.

 

 

Learn More Tips like this – Enroll to the Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers(special limited-time discount included in link).

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Essential SQL Server Development Tips for SQL Developers - Online Course
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

Upgrade your Tech Skills – Learn all about Azure SQL Database

Enroll to our online course on Udemy titled “Introduction to Azure SQL Database for Beginners” and get lifetime access to high-quality lessons and hands-on guides about all aspects of Azure SQL Database.

Introduction to Azure SQL Database (Online Course - Lifetime Access)
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

 

Featured Online Courses:

 

Read Also:

 

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHubTV)!

Like our Facebook Page!

Check our SQL Server Administration articles.

Check out our latest software releases!

Check our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub