How to Pass an Array to a SQL Server Stored Procedure?

Pass the array to the SQL Server storage procedure

In the scenario that needs to pass data from C# as an array to SQL Server stored procedure, there are many ways. This article will explore several methods, depending on the SQL Server version used.

SQL Server 2016 (or higher version)

Using string_split () or openjson (), you can pass the separation list or JSON to the storage procedure.

String_Split ()

<code class="language-sql">CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List varchar(max)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT value FROM STRING_SPLIT(@List, ',');
END
GO</code>

Openjson ()

<code class="language-sql">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
GO</code>

SQL Server 2008 (or higher version)

Custom data type and table value parameters are used for array transmission.

SQL database object

<code class="language-sql">CREATE TYPE dbo.IDList
AS TABLE
(
  ID INT
);
GO

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List AS dbo.IDList READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT ID FROM @List; 
END
GO</code>

C# code

<code class="language-csharp">// 获取员工 ID
int[] employeeIds = GetEmployeeIds();

// 创建 DataTable 并使用员工 ID 填充
DataTable tvp = new DataTable();
tvp.Columns.Add(new DataColumn("ID", typeof(int)));
foreach (int id in employeeIds)
    tvp.Rows.Add(id);

// 将 DataTable 作为参数添加
using (conn)
{
    SqlCommand cmd = new SqlCommand("dbo.DoSomethingWithEmployees", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@List", tvp);
    tvparam.SqlDbType = SqlDbType.Structured;
    tvparam.TypeName = "dbo.IDList";

    // 执行查询
}</code>

SQL Server 2005

Introduction to the custom disassembly function for array transmission:

SQL function

<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
  );
GO</code>

Storage procedure

C# 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
GO</code>

Pass the array as a list of commas.

The above is the detailed content of How to Pass an Array to a SQL Server Stored Procedure?. For more information, please follow other related articles on the PHP Chinese website!