This is one of the interesting feature which I like most. Instead of passing values from collection one by one pass entire collection to stored procedure as a table value parameter.
I know above statement is not digestive enough so lets 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 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 below figure
Now think you have .net program and you have to call this stored procedure more than 100 times .
I know you are bit scary.
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
To create Table value parameter you have to choose user define data type as shown in below figure
Now you can modify the template script in my case I am creating a student type User defined table type as shown in below figure . You will find I am using all the column which I need to insert into tblStudent table
Now I am writing a new stored procedure which use this table type as shown in 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 that the table value parameter should be READONLY when passes in a stored procedure
Step 5:- Now let see how to call this stored procedure which has student type table value parameter
Now when we run above snap statement we inserted 3 rows in tblStudent.
Now lets 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.
Isn’t it made our life easy ? I am big fan of this feature.
I hope if you use you will also.
Hope this tip may help you somewhere.
Thanks & Enjoy !!!