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

)

go

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

as

set nocount ON

if @range <>0

begin

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

and UnitPrice>@range

return

end

Go

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