|
Explain Table Value Parameters (TVP) in sql server 2008A 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.
|
