Table Value Parameters (TVPs)

Table Value Parameters (TVPs):

Following is description from BOL

http://msdn.microsoft.com/en-us/library/bb510489.aspx
Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.
Table-valued parameters are like parameter arrays in OLE DB and ODBC, but offer more flexibility and closer integration with Transact-SQL. Table-valued parameters also have the benefit of being able to participate in set-based operations.
Transact-SQL passes table-valued parameters to routines by reference to avoid making a copy of the input data. You can create and execute Transact-SQL routines with table-valued parameters, and call them from Transact-SQL code, managed and native clients in any managed language.

Here is an example of how to use TVPs

/*CREATE A SAMPLE TABLE TO WORK WITH*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductType]') AND type in (N'U'))
DROP TABLE [dbo].[ProductType]
GO
CREATE TABLE [ProductType]
(
[ProductTypeID] INT IDENTITY(1,1) PRIMARY KEY,
[ProductTypeName] VARCHAR(30)
)
GO

/*USE CREATE TYPE TO CREATE A TVP WHICH WILL ACCEPT TABULAR DATA AND INSERT IT TO THE TABLE*/
IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id
WHERE st.name = N'ProductType' AND ss.name = N'dbo')
DROP TYPE [dbo].[ProductType]
GO
CREATE TYPE [dbo].ProductType AS TABLE(
[ProdTypeName] [varchar](30) NULL
)
GO
/*
--CREATE STORED PROCEDURE WHICH WILL INPUT PARAMETER AS THE TVP WHICH WE CREATED
--THE DATA IN TVP WILL BE INSERTED INTO THE TABLE
*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertProductType]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InsertProductType]
GO
CREATE PROCEDURE InsertProductType
@InsertProdType_TVP [ProductType] READONLY
AS
INSERT INTO [ProductType]([ProductTypeName])
SELECT * FROM @InsertProdType_TVP;
GO

/*
--THIS IS HOW YOU CAN USE A TVP TO PASS TABULAR DATA TO STORED PROCEDURE
--THE SP WILL INTURN INSERT IT TO THE TABLE
*/
DECLARE @InsertProdTypeTVP AS ProductType;

INSERT INTO @InsertProdTypeTVP([ProdTypeName])
VALUES ('Farming'),
('Sporting'),
('Software'),
('Arms'),
('Ammo');

--select * from @InsertProdTypeTVP

EXEC InsertProductType @InsertProdTypeTVP;
GO

/*RUN SELECT ON BASE TABLE TO CONFIRM THAT DATA IS INSERTED*/
select * from [ProductType]

Benefits:
1. They provide a simpler way to pass a result set to a stored procedure
2. Pass through allows us to avoid multiple calls to the stored procedure
3. TVPs save round trip to the server by passing all data in single call to the stored procedure
4. TVPs provide more efficient and scalable way to process strings of data
Restrictions:
1. TVPs can not be used in SELECT INTO or INSERT EXEC
2. TVPs are READONLY parameters
Drawbacks:
1. It works similar to table variables so there are no statistics associated with TVPs. This can result in occasional suboptimal plans
2. No Design time syntax checking
3. Run time error handling is minimal
4. In case table has identity columns and pass through query is inserting in to the table then we can’t get resulting identity value because they can’t return data

Note: TVPs are only available in SQL Server 2008 onwards