Updating table with table variable

*/ CREATE TYPE Location Table Type AS TABLE ( Location Name VARCHAR(50) , Cost Rate INT ); GO /* Create a procedure to receive data for the table-valued parameter. usp_Insert Production Location @TVP Location Table Type READONLY AS SET NOCOUNT ON INSERT INTO Adventure Works2012. Location (Name , Cost Rate , Availability , Modified Date) SELECT *, 0, GETDATE() FROM @TVP; GO /* Declare a variable that references the type.

*/ DECLARE @Location TVP AS Location Table Type; /* Add data to the table variable.

The following example uses Transact-SQL and shows you how to create a table-valued parameter type, declare a variable to reference it, fill the parameter list, and then pass the values to a stored procedure.

USE Adventure Works2012; GO /* Create a table type.

Expense Report ID)) CREATE PROCEDURE Expense Report_Assign Approver ( @Expense Report IDs XML ) AS BEGIN DECLARE @ERTable Variable TABLE ( Expense Report ID INT, Expense Report Project ID INT, Approver ID INT) CREATE TABLE #ERTemp Table ( Expense Report ID INT, Expense Report Project ID INT, Approver ID INT ) INSERT INTO @ERTable Variable (Expense Report ID) SELECT Param Values.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.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.