Table Valued Parameters

Table Valued Parameters are new to SQL Server 2008. They allow us to pass table variable to stored procedure. The same function can be accomplished by temporary tables, but table valued provide better performance and complete dataset in a table can be passed to stored procedure. In an application there can be scenarios when we need same joins from different tables for many different web forms, in such scenarios table valued can be very useful. This will be more clear from example below:

We will be using AdventureWorks database to implement this.

At first we need to create a User Defined Table type as below:

create TYPE CustomerSales_tblType as table


firstname varchar(20),

Lastname varchar(20),

ProductID varchar(5),

UnitPrice decimal(8,2),

OrderQty int,

ProductName varchar(100),

Orderdate datetime



Once we have created a UDF we can use that as datatype to pass a table as a parameter

create procedure CustomerSalesDetail

@range float=0,

@date date=null,

@CustomeSaleTab CustomerSales_tblType READONLY


set nocount ON

if @range <>0


select * from @CustomeSaleTab where Orderdate<=@date and orderdate>=DATEADD(month,-12,@date)

and UnitPrice>@range




Below is how we can pass the complete table as parameter

declare @CustomeSaleTab CustomerSales_tblType

insert @CustomeSaleTab

select C.FirstName ,C.LastName , o.ProductID ,UnitPrice ,OrderQty ,p.Name ,h.OrderDate

from SalesLT.Customer c

inner join SalesLT.SalesOrderHeader h

ON c.CustomerID =h.CustomerID

inner join SalesLT.SalesOrderDetail o

ON h.SalesOrderID=o.SalesOrderID

inner join SalesLT.Product p

ON o.ProductID =p.ProductID


Execute the procedure and pass the complete table as parameter


exec CustomerSalesDetail 100,'2004-10-11',@CustomeSaleTab