This is a new feature introduced in SQL Server 2008. Table-valued or table type parameters in SQL server provide an option for the Client Applications to pass multiple rows of Data to Stored Procedure. In this post, we discuss how to use table type parameters in SQL Server.

 

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.

 

First, we need to create a User Defined Table Type that can be reused in multiple stored procedures as input table parameter data type.

 

Now let us create a simple stored procedure that takes CustomerType User Define Table Type which we have created previously.

 

Using Stored Procedure With table type parameters in SQL server.

Table type Parameters in Sql Server

Leave a Comment