Table Value Parameters are a small new T-SQL enhancement in SQL Server 2008 which allows us to pass through a TABLE value instance as a parameter to a stored procedure. This is a welcome new addition to support of the TABLE type as it helps in the situations where we want to pass in a row like structure without having to separate it out into arguments and then recombining it as part of the query.
Making use of it is fairly simple, let’s look at an example:
First we define the type
create type OrderTableType as TABLE(ProductId int, ProductName varchar(255), Quantity int)
Then we define the stored procedure (implementation excluded)
create procedure dbo.UpdateProductFromDailyOrders( @orders OrderTableType READONLY )
First we need to declare an instance of the type and populate it with some data
declare @todaysOrders OrderTableType
insert into @todaysOrders
select p.Id, p.ProductName, sum(o.Quantity)
from [Order] o inner join Product p on o.ProductId = p.Id
where o.OrderDate = '2007-01-05'
group by p.Id, p.ProductName
Then can now make use of it with the stored procedure
exec dbo.UpdateProductFromDailyOrders @todaysOrders
Easy!
The only caveat you have is that the structure passed in has to be specified as READONLY preventing any modification of the data inside the parameter.