Multiple Approaches for Passing Array Parameters to SQL Server Stored Procedures

Introduction

In modern database application development, there is often a need to pass arrays or collection data as parameters to stored procedures. This requirement is quite common in business scenarios, such as querying detailed information based on multiple employee IDs, batch updating statuses, etc. SQL Server provides multiple implementation approaches, each with its applicable scenarios and version requirements.

Table-Valued Parameters Method (SQL Server 2008 and Above)

Table-Valued Parameters (TVP), introduced in SQL Server 2008, provide a type-safe and high-performance solution for array passing. First, you need to create a user-defined table type in the database:

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

In the C# client code, you need to use DataTable to construct the parameter:

int[] employeeIds = GetEmployeeIds();

DataTable tvp = new DataTable();
tvp.Columns.Add(new DataColumn("ID", typeof(int)));

foreach(var id in employeeIds)
    tvp.Rows.Add(id);

using (SqlConnection conn = new SqlConnection(connectionString))
{
    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";
    conn.Open();
    // Execute query and process results
}

The main advantages of table-valued parameters include strong type safety, excellent performance, and good code readability. Parameter definitions are clear and explicit, eliminating the need to guess delimiter types, resulting in lower maintenance costs.

String Splitting Method (SQL Server 2016 and Above)

For SQL Server 2016 and newer versions, you can use the built-in STRING_SPLIT function to handle comma-separated strings:

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List varchar(max)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT value FROM STRING_SPLIT(@List, ',');
END
GO

EXEC dbo.DoSomethingWithEmployees @List = '1,2,3';

Another option is to use the OPENJSON function, which is particularly suitable for handling JSON-formatted array data:

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

These built-in functions simplify string processing logic and provide better performance and maintainability. In practical applications, you can refer to relevant technical documentation to optimize the performance of string splitting operations.

Custom Splitting Function (SQL Server 2005)

For older SQL Server 2005 versions, you can create custom splitting functions to implement array parameter passing:

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

CREATE PROCEDURE dbo.DoSomethingWithEmployees
  @List VARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;
  
  SELECT EmployeeID = Item FROM dbo.SplitInts(@List, ','); 
END
GO

In the C# code, you only need to convert the array to a comma-separated string:

string employeeIds = string.Join(",", GetEmployeeIds());
// Then pass as string parameter to stored procedure

Performance Comparison and Selection Recommendations

From a performance perspective, table-valued parameters typically offer the best execution efficiency, especially when handling large amounts of data. String splitting methods perform well in SQL Server 2016 and above, while custom splitting functions are a good choice for older versions.

When selecting a specific implementation approach, consider the following factors:

• SQL Server version compatibility requirements
• Data volume size and performance needs
• Acceptable level of code maintenance complexity
• Team's technical stack familiarity

In practical development, table-valued parameters are the preferred solution due to their type safety and excellent performance characteristics. For simple query scenarios, string splitting methods provide sufficient convenience. Regardless of the chosen approach, thorough testing is recommended to ensure business requirements are met.