SQL Server使用表值参数的实现示例

在SQL Server中，表值参数（Table-Valued Parameters, TVPs）提供了一种将多行数据作为参数传递给存储过程或函数的方法。这种方法在需要处理批量数据或复杂数据集时非常有用，因为它比使用多个单个参数或使用临时表或表变量更灵活和高效。

1、简介

适用于：

• SQL Server
• Azure SQL 数据库
• Azure SQL 托管实例

参考官方文档地址
https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-ver16

表值参数是使用用户定义的表类型来声明的。 使用表值参数，可以不必创建临时表或许多参数，即可向 Transact-SQL 语句或例程（如存储过程或函数）发送多行数据。

表值参数与 OLE DB 和 ODBC 中的参数数组类似，但具有更高的灵活性，且与 Transact-SQL 的集成更紧密。 表值参数的另一个优势是能够参与基于数据集的操作。

Transact-SQL 通过引用向例程传递表值参数，以避免创建输入数据的副本。 可以使用表值参数创建和执行 Transact-SQL 例程，并且可以使用任何托管语言从 Transact-SQL 代码、托管客户端以及本机客户端调用它们。

2、优点

就像其他参数一样，表值参数的作用域也是存储过程、函数或动态 Transact-SQL 文本。 同样，表类型变量也与使用 DECLARE 语句创建的其他任何局部变量一样具有作用域。 可以在动态 Transact-SQL 语句内声明表值变量，并且可以将这些变量作为表值参数传递到存储过程和函数。

表值参数具有更高的灵活性，在某些情况下，可比临时表或其他传递参数列表的方法提供更好的性能。 表值参数具有以下优势：

• 首次从客户端填充数据时，不获取锁。
• 提供简单的编程模型。
• 允许在单个例程中包括复杂的业务逻辑。
• 减少到服务器的往返。
• 可以具有不同基数的表结构。
• 是强类型。
• 使客户端可以指定排序顺序和唯一键。
• 在用于存储过程时像临时表一样被缓存。 从 SQL Server 2012 (11.x) 及更高版本开始，在参数化查询中，表值参数也将缓存。

3、权限

要创建用户定义表类型的实例或使用表值参数调用存储过程，用户必须对该类型或包含该类型的架构或数据库具有 EXECUTE 和 REFERENCES 权限。

4、限制

表值参数有下面的限制：

• SQL Server 不维护表值参数列的统计信息。
• 表值参数必须作为输入 READONLY 参数传递到 Transact-SQL 例程。 不能在例程体中对表值参数执行诸如 UPDATE、DELETE 或 INSERT 这样的 DML 操作。
• 不能将表值参数用作 SELECT INTO 或 INSERT EXEC 语句的目标。 表值参数可以在 SELECT INTO 的 FROM 子句中，也可以在 INSERT EXEC 字符串或存储过程中。

5、表值参数与 BULK INSERT 操作

表值参数的使用方法与其他基于数据集的变量的使用方法相似；但是，频繁使用表值参数将比大型数据集要快。 大容量操作的启动开销比表值参数大，与之相比，表值参数在插入数目少于 1000 的行时具有很好的执行性能。

重用的表值参数可从临时表缓存中受益。 这一表缓存功能可比对等的批量插入操作提供更好的伸缩性。 小型行插入操作可以通过使用参数列表或批量语句（而不是 BULK INSERT 操作或表值参数）来获得小的性能改进。 但是，这些方法在编程上不太方便，并且随着行的增加，性能会迅速下降。

表值参数在执行性能上与对等的参数阵列实现相当甚至更好。

6、示例

6.1、创建表值类型

首先，你需要定义一个表类型，该类型将用作表值参数的基础。这可以通过CREATE TYPE语句完成。

CREATE TYPE dbo.Emp_TableType AS TABLE
(
    ID INT NOT NULL,
    Name NVARCHAR(50),
    Age INT
);

6.2、创建测试表及插入数据

CREATE TABLE dbo.emp
(
    ID INT NOT NULL CONSTRAINT PK_emp_id PRIMARY KEY,
    Name NVARCHAR(50),
    Age INT
)
INSERT INTO dbo.emp VALUES(null,N'superdb',28),(null,N'sqlserver',20)

SELECT * FROM dbo.emp

ID          Name                                               Age
----------- -------------------------------------------------- -----------
1           superdb                                            28
2           sqlserver                                          20

(2 行受影响)

6.3、创建存储过程示例

一旦定义了表类型，你就可以在存储过程或函数中使用该类型作为表值参数了

下面的示例使用 Transact-SQL 并展示了如何执行以下操作：创建表值参数类型，声明变量来引用它，填充参数列表，然后将值传递到数据库中的存储过程。

CREATE PROCEDURE sp_InsertUpdateEmp
  @TVP P_TableType READONLY
AS
BEGIN
    MERGE INTO dbo.emp AS Target  
    USING @TVP AS Source ON Target.ID = Source.ID  
    WHEN MATCHED THEN  
      UPDATE SET Target.Name = Source.Name,Target.Age=Source.Age  
    WHEN NOT MATCHED BY TARGET THEN  
      INSERT (ID, Name,Age) VALUES (Source.ID, Source.Name,Source.Age);
END 

注意，表值参数在存储过程中必须是只读的（READONLY）。

6.4、 调用存储过程示例

调用带有表值参数的存储过程时，你需要传递一个与表类型相匹配的数据表。这通常通过定义一个表变量并使用INSERT语句填充数据来完成，或者使用应用程序代码（如C#或VB.NET）直接构造表值参数。

-- 使用表变量
DECLARE @MyTableVar AS dbo.P_TableType;

INSERT INTO @MyTableVar (ID, Name, Age) VALUES (null, 'Alice', 30);
INSERT INTO @MyTableVar (ID, Name, Age) VALUES (null, 'Bob', 25);
INSERT INTO @MyTableVar (ID, Name, Age) VALUES (null, 'tvp', 34);

EXEC dbo.sp_InsertUpdateEmp @TVP = @MyTableVar;

6.5、检查验证表数据

SELECT * FROM dbo.emp

ID          Name                                               Age
----------- -------------------------------------------------- -----------
1           Alice                                              30
2           Bob                                                25
3           tvp                                                34

(3 行受影响)

7、 注意事项

• 确保表值类型在调用存储过程之前已经被创建。
• 表值参数在存储过程中必须是只读的。
• 表值参数提供了一种灵活且性能优化的方式来处理批量数据。

通过表值参数，SQL Server允许开发人员和数据库管理员以更有效、更安全的方式处理复杂的数据集，这在处理大量数据或需要高度定制数据输入的场景中特别有用。

到此这篇关于SQL Server使用表值参数的实现示例的文章就介绍到这了,更多相关SQL 表值参数内容请搜索插件窝以前的文章或继续浏览下面的相关文章希望大家以后多多支持插件窝！