Table-Valued Parameters in Sql Server

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

10 thoughts on “Table-Valued Parameters in Sql Server

  1. Team “sqlhint.com” is best bcoz they dont give hint, but they Explane concept in depth in simple manner ! Thanks ! sqlhint.com

    1. Protected ==> Acess specifier , if any method / properties / variables having protected access specifier in a parent class, then it will be inherited to the child class, if we have inheritance.
      How it will be inherited ==> It will be inherited with Private access specifier.
      polymorphism => Means more than one form. Function Overloading / Overriding.
      Take Function overloading => Same method name with different no. of arguments & diff. return types in the same class. Then you can achieve polymorphism ( static )
      Take Function Overridding ==> Same signature of the methods in parent & child classes. Here also achieve polymorphism ( runtime )

      Coming to your point.
      if we use protected access specifier in Parent , then it will become a private when we do function overridding. So, how do we do override. In parent, we will have virtual keyword with access specifier & in child we have override . The issue is, compiler will not allow with private access specifier for Virtual & abstract members. This is the rule.

      I hope you are understood from this.

Leave a Reply

Your email address will not be published. Required fields are marked *