SQL Server 存储过程使用表值参数(TVP)示例

表值参数应用目标

利用 Table-Valued Parameter 将一个具有特定列结构的查询结果集一次性传入存储过程，并在过程中执行批量插入操作。

准备条件

1. 环境要求：数据库引擎为 SQL Server 2008 或更高版本。
2. 权限要求：拥有 CREATE TYPE 和 ALTER PROCEDURE 权限。

实施步骤

第1步：定义用户自定义表类型

目的：确立要传入数据的结构，包括列名及其数据类型。

说明：通过 CREATE TYPE 语句创建一个名为 LocationTableType 的类型，包含 LocationName 和 CostRate 两列。

第2步：编写接收参数的存储过程

目的：创建能够消费上述表类型的逻辑单元。

动作与参数：

• 声明参数变量时必须标注为 READONLY，这是 TVP 的唯一访问限制模式。
• 在 INSERT INTO 语句中直接引用该变量。

第3步：调用并验证结果

入口：执行 Transact-SQL 脚本。

具体动作：

1. 声明与该表类型同名的局部变量。
2. 将目标源数据（如其他表查询结果）插入到该局部变量中。
3. 将变量作为实参传递给存储过程。

/* --- 1. 创建表类型 (仅执行一次) --- */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50),
  CostRate INT );
GO
 
/* --- 2. 创建以表值参数为输入的存储过程 --- */
CREATE PROCEDURE dbo.usp_InsertProductionLocation
  @TVP LocationTableType READONLY -- 注意此处必须加 READONLY
  AS
  BEGIN
    SET NOCOUNT ON;
    INSERT INTO Production.Location
        (Name, CostRate, Availability, ModifiedDate)
      SELECT *, 0, GETDATE()
      FROM @TVP;
  END;
GO
 
/* --- 3. 声明表值参数变量并赋值 --- */
DECLARE @LocationTVP AS LocationTableType;
 
/* 将外部查询的数据填入变量 */
INSERT INTO @LocationTVP(LocationName, CostRate)
  SELECT Name, 0.00
  FROM Person.StateProvince;
 
/* --- 4. 传递变量至存储过程 --- */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

完成判断

执行完毕后，检查目标表 Production.Location 的记录数或最新插入内容。若行数与查询源 Person.StateProvince 一致且无报错信息，则表明表值参数传递成功。

简要总结

表值参数是 SQL Server 中进行批数据处理的高效手段。相比传统的游标、循环或复杂的 XML/JSON 解析，TVP 语法更贴近原生 SQL，且在客户端（如 C#、Java 等）集成时能显著减少网络往返开销。以上就是 SQL Server 存储过程中使用表值参数作为输入参数的详细内容，更多关于 T-SQL 优化技巧的资料请关注本站其它相关文章！