SQL Server 2008 Table-Valued parameter usage

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

1  UseAdventureWorks;2 GO3 4 /*creating table-valued parameter types*/5 CREATETYPE Locationtabletype as TABLE 6(LocationNameVARCHAR( -)7, CostrateINT );8 GO9 Ten /*Create a procedure to get the table-valued parameter data*/ One CREATE PROCEDUREusp_insertproductionlocation A     @TVPLocationtabletype READONLY -   as  -     SETNOCOUNT on the     INSERT  into [AdventureWorks].[Production].[ Location] -([Name] -,[costrate] -,[Availability] +,[ModifiedDate]) -         SELECT *,0,GETDATE() +          from  @TVP; A  GO at  - /*declare a variable to reference the type*/ - DECLARE @LocationTVP  -  asLocationtabletype; -  - /*ADD data to the table variable.*/ in INSERT  into @LocationTVP(LocationName, costrate) -     SELECT [Name],0.00 to      from  +     [AdventureWorks].[ Person].[StateProvince]; -  the /*passing data from a table variable to a stored procedure*/ * EXECUsp_insertproductionlocation@LocationTVP; $ GO

SQL Server 2008 Table-Valued parameter usage