USE AdventureWorks;

 GO

 /*创建表值参数类型 */

 CREATE TYPE LocationTableType AS TABLE

 ( LocationName VARCHAR(50)

 , CostRate INT );

 GO

 /* 创建一个过程来获得该表值参数数据 */

 CREATE PROCEDURE usp_InsertProductionLocation

     @TVP LocationTableType READONLY

  AS

     SET NOCOUNT ON

     INSERT INTO [AdventureWorks].[Production].[Location]

            ([Name]

            ,[CostRate]

            ,[Availability]

            ,[ModifiedDate])

         SELECT *, 0, GETDATE()

         FROM  @TVP;

  GO

 /*声明一个变量来引用该类型*/

 DECLARE @LocationTVP

 AS LocationTableType;

 /* Add data to the table variable. */

 INSERT INTO @LocationTVP (LocationName, CostRate)

     SELECT [Name], 0.00

     FROM

     [AdventureWorks].[Person].[StateProvince];

 /* 把表变量的数据传递给存储过程 */

 EXEC usp_InsertProductionLocation @LocationTVP;

 GO