SQLServer存储过程中使用表值作为输入参数示例

/* 创建表类型.*/
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO
 
/* 创建一个存储过程以表值参数作为输入. */
CREATE PROCEDURE dbo. usp_InsertProductionLocation
  @TVP LocationTableType READONLY
  AS
  SET NOCOUNT ON
  INSERT INTO Production.Location
      (Name
      ,CostRate
      ,Availability
      ,ModifiedDate)
    SELECT *, 0, GETDATE()
    FROM @TVP;
    GO
 
 
/* 声明表值参数变量.*/
DECLARE @LocationTVP ASLocationTableType;
 
/* 将数据插入表值变量*/
INSERT INTO @LocationTVP(LocationName, CostRate)
  SELECT Name, 0.00
  FROM Person.StateProvince;
 
/* 将变量传递给存储过程*/
EXEC usp_InsertProductionLocation@LocationTVP;
GO