What is TVP (Table Value Parameter) & How To Use It? TIP #57

This is one of the interesting features which I like the most. Instead of passing values from collection one by one, pass the entire collection to stored procedure as a table value parameter.

I know the above statement is not digestive enough, so let's understand this by an example.

Suppose I have a table tblStudent with 4 columns studentId, FirstName, LastName, and class as shown in below figure:

Now, I need to insert value in this table so I have created a simple stored procedure as shown in the below figure:

Now, I can easily insert values into it by calling the stored procedure for each student FirstName, LastName, course.

Suppose I need to insert 4 records in the table, then I need to call this stored procedure 4 times as shown in the below figure:

Now think you have a .NET program and you have to call this stored procedure more than 100 times .

I know you are a bit scared.

Not to worry, thanks to SQL Server again, by providing Table value parameter to handle such kind of situation.

Now let me explain how to create table value parameter and call it to resolve such problem step by step.

Step 1

To create Table value parameter, you have to choose user defined data type as shown in the below figure:

Step 2

Now you can modify the template script. In my case, I am creating a student type User defined table type as shown in the below figure. You will find I am using all the columns which I need to insert into tblStudent table.

Step 3

Now I am writing a new stored procedure which uses this table type as shown in the below figure:

You find in the above snap we have created a procedure with student type parameter which we have created.

The point to remember here is that the table value parameter should be READONLY when passed in a stored procedure.

Step 4

Now let's see how to call this stored procedure which has student type table value parameter.

Now when we run the above snap statement, we inserted 3 rows in tblStudent.

Now let's consider the above problem calling stored procedure 1000 times. Instead of that, you will call stored procedure only once with this amazing Table value parameter feature.

You can pass any collection and data table as a parameter from .NET.

Hasn’t it made our life easy? I am a big fan of this feature.

I hope if you use it, you will also like it.

Hope this tip may help you somewhere.

Thanks & enjoy !!!

RJ !!!

Filed under: CodeProject, Database, DENALI, Performance, Sql server, SQL SERVER 2008 - R2, SQL SERVER 2014, TIPS Tagged: Stored Procedure, Table Value parameter, TVP