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

/ 创建表类型./
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