MS SQL Server 2008 Interview Question:

What is Table Value Parameters (TVP)?

Submitted by: Administrator

A user defined tables are allowed between queries using the Table Value Parameters feature of SQL Server 2008. It also supports defining the tables between a client and a server. Querying, joining, inserting values, updating values etc., can be done as is being done with a normal table. Instead of a query taking a long list of parameters, they simple take TVP as a parameter.

For creating TVP, one need to define a user defined type and the columns which the TVP would hold. The following example creates a customer type which holds an id and name.

CREATE TYPE Customer AS TABLE (id int, CustomerName nvarchar(50))

A dummy table is created for persisting the information.
CREATE TABLE Customers (id int, CustomerName nvarchar(50)) GO

A procedure can also be created which takes a single parameter as a Table Value Parameter. Data can be inserted into two different tables, however from the outside it is a single object and only a single stored procedure is being called.

CREATE Procedure AddCustomers(@customer Customer READONLY) AS
INSERT INTO Customers SELECT id, CustomerName FROM @customer
GO

The TVP as parameter must have the READONLY attribute and TVPs are basically temporary tables persisted on the server in tempdb.
Submitted by: Administrator