This is a new feature intorduced in Sql Server 2008. Table-Valued Parameters provides option for the Client Applications to pass multiple rows of Data to Stored Procedure.

Prior to this, if we were needed to pass multiple rows of Data from client application to Sql Server, then we use to model the input data as xml and pass it to the stored procedure and in Stored Procedure convert this xml to a table variable/temporary table.

In this article we will not only go over this Table-Valued Parameter we will also understand how to call the Stored Procedure with Table-Valued Parameter from Sql Server and C# .Net Code.

Table-Valued User Defined Data Type
First we need to Create a User Defined Table Type which can be reused in multiple stored procedures as input table parameter data type.

Stored Procedure with Table-Valued input Parameter
Now let us create a simple stored procedure which takes CustomerType User Define Table Type which we have created previously. 

Using Stored Procedure With Table Valued Parameter in Sql Server.

 

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.