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