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.

CREATE TYPE dbo.CustomerTableType AS TABLE
( 
 [CustomerID] INT,
 [Name]  VARCHAR(50)
)
GO

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

CREATE PROCEDURE dbo.GetCustomerDetails
(
 @Customers AS dbo.CustomerTableType READONLY
)
AS
BEGIN
 SET NOCOUNT ON

 SELECT *
 FROM @Customers
END

Using Stored Procedure With Table Valued Parameter in Sql Server

Declare @CustomerDetails As dbo.CustomerTableType
Insert Into @CustomerDetails Values(1,'Basavaraj'),
         (2,'Monty'),
         (3,'Shashank') 

Exec dbo.GetCustomerDetails @CustomerDetails
GO
Result:
CustomerID   Name
----------- ------------
1           Basavaraj
2           Monty
3           Shashank

Calling Stored Procedure with Table Valued Parameter from C# Code
Below is a sample C# Code example which calls the GetCustomerDetails Stored Procedure with Table-Valued Parameter which is created in the previous section.

using System;
using System.Data;
using System.Data.SqlClient;
namespace TableValuedParameter
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create and open a connection object
            SqlConnection conn = new SqlConnection(
               "ENTER A VALID CONNECTION STRING");
            conn.Open();

            //Create a command object specify the stored procedure
            SqlCommand cmd = new SqlCommand("dbo.GetCustomerDetails", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            //PrePare the rows of Data to be Passed to the Stored Procedure            
            DataTable dataTable = new DataTable("Customer");
            dataTable.Columns.Add("Id", typeof(Int32));
            dataTable.Columns.Add("Name", typeof(string));
            dataTable.Rows.Add(1, "Basavaraj");
            dataTable.Rows.Add(2, "Monty");
            dataTable.Rows.Add(3, "Shashank");
            //Add the Table-Valued Parameter value to the Command Object            
            SqlParameter param = new SqlParameter("@Customers", dataTable);
            param.SqlDbType = SqlDbType.Structured;           
            cmd.Parameters.Add(param);
            // Execute the command
            SqlDataReader rdr = cmd.ExecuteReader();
            // iterate through results, printing each record to the console
            while (rdr.Read())
            {
                Console.WriteLine("Employee ID: {0} Name: {1}"
                                            ,rdr["CustomerID"],rdr["Name"]);
            }
            conn.Close();
            rdr.Close();
            Console.ReadKey();
        }
    }
}

Result Seen on the console: 
Employee ID: 1 Name: Basavaraj
Employee ID: 2 Name: Monty
Employee ID: 3 Name: Shashank

EXECUTE Permission ON Table Type User Defined Type: Eventhough we have Execute permission on the Stored Procedure, we still need to give Execute permission on the Table Type User Defined Type. Below is the syntax for granting execute persmission on the Table Type User Defined Type

GRANT EXECUTE ON TYPE::dbo.CustomerTableType TO UserName