How Can I Pass Arrays into SQL Server Stored Procedures?

Pass the array to the SQL Server storage procedure

The array is passed to the SQL Server storage process is very useful in various scenarios, such as using the employee list as a table to connect to other tables. This article discusses the different methods of passing the array to the storage procedure according to different versions of SQL Server.

SQL Server 2016 (or higher version)

For SQL Server 2016 and higher versions, you can pass the separation list or JSON, and use

String_Split () or Openjson () function.

<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 (or higher version)

In SQL Server 2008 and higher versions, you can create a user definition type (UDT) to represent the array.

<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

If you are using SQL Server 2005, you can use a custom function to divide the list.

<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>

Summary:

The method of using table value parameters (TVP) simplifies the maintenance of the solution to use it, and is usually higher than other implementations including XML and string divisions. This involves a step similar to creating a user -defined XML mode, but in experience, it is easier to manage, maintain and read.

The above is the detailed content of How Can I Pass Arrays into SQL Server Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!