将数组传递到 SQL Server 存储过程在各种场景中都非常有用,例如使用员工列表作为表来与其他表连接。本文探讨了根据不同版本的 SQL Server 将数组传递到存储过程的不同方法。
对于 SQL Server 2016 及更高版本,您可以传递分隔列表或 JSON,并使用 STRING_SPLIT() 或 OPENJSON() 函数。
<code class="language-sql">-- STRING_SPLIT() 示例 CREATE PROCEDURE dbo.DoSomethingWithEmployees @List varchar(max) AS BEGIN SET NOCOUNT ON; SELECT value FROM STRING_SPLIT(@List, ','); END</code>
<code class="language-sql">-- OPENJSON() 示例 CREATE PROCEDURE dbo.DoSomethingWithEmployees @List varchar(max) AS BEGIN SET NOCOUNT ON; SELECT value FROM OPENJSON(CONCAT('["', REPLACE(STRING_ESCAPE(@List, 'JSON'), ',', '","'), '"]')) AS j; END</code>
在 SQL Server 2008 及更高版本中,您可以创建用户定义类型 (UDT) 来表示数组。
<code class="language-sql">-- 创建 UDT CREATE TYPE dbo.IDList AS TABLE ( ID INT ); -- 创建存储过程 CREATE PROCEDURE dbo.DoSomethingWithEmployees @List AS dbo.IDList READONLY AS BEGIN SET NOCOUNT ON; SELECT ID FROM @List; END</code>
如果您使用的是 SQL Server 2005,则可以使用自定义函数来分割列表。
<code class="language-sql">-- 创建函数 CREATE FUNCTION dbo.SplitInts ( @List VARCHAR(MAX), @Delimiter VARCHAR(255) ) RETURNS TABLE AS RETURN ( SELECT Item = CONVERT(INT, Item) FROM ( SELECT Item = x.i.value('(./text())[1]', 'varchar(max)') FROM ( SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.') ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y WHERE Item IS NOT NULL );</code>
<code class="language-sql">-- 创建存储过程 CREATE PROCEDURE dbo.DoSomethingWithEmployees @List VARCHAR(MAX) AS BEGIN SET NOCOUNT ON; SELECT EmployeeID = Item FROM dbo.SplitInts(@List, ','); END</code>
总结:
使用表值参数 (TVP) 的方法简化了使用它的解决方案的可维护性,并且与包括 XML 和字符串分割在内的其他实现相比,通常具有更高的性能。这涉及与创建用户定义 XML 模式类似的步骤,但在经验中,它更容易管理、维护和阅读。
以上是如何将数组传递到SQL Server存储的过程中?的详细内容。更多信息请关注PHP中文网其他相关文章!