Passing array parameters to a stored procedure

The best ever articles on this matter are by Erland Sommarskog:

  • Arrays and Lists in SQL Server 2008 - Using Table-Valued Parameters
  • Arrays and Lists in SQL Server 2005 and Beyond- When TVPs Do Not Cut it
  • Arrays and Lists in SQL Server 2000 and Earlier

He covers all options and explains pretty well.

Sorry for the shortness of the answer, but Erland's article on Arrays is like Joe Celko's books on trees and other SQL treats :)


There is a great discussion of this on StackOverflow that covers many approaches. The one I prefer for SQL Server 2008+ is to use table-valued parameters. This is essentially SQL Server's solution to your problem--passing in a list of values to a stored procedure.

The advantages of this approach are:

  • make one stored procedure call with all your data passed in as 1 parameter
  • table input is structured and strongly typed
  • no string building/parsing or handling of XML
  • can easily use table input to filter, join, or whatever

However, take note: If you call a stored procedure that uses TVPs via ADO.NET or ODBC and take a look at the activity with SQL Server Profiler, you will notice that SQL Server receives several INSERT statements to load the TVP, one for each row in the TVP, followed by the call to the procedure. This is by design. This batch of INSERTs needs to be compiled every time the procedure is called, and constitutes a small overhead. However, even with this overhead, TVPs still blow away other approaches in terms of performance and usability for the majority of use cases.

If you want to learn more, Erland Sommarskog has the full skinny on how table-valued parameters work and provides several examples.

Here is another example I concocted:

CREATE TYPE id_list AS TABLE (
    id int NOT NULL PRIMARY KEY
);
GO

CREATE PROCEDURE [dbo].[tvp_test] (
      @param1           INT
    , @customer_list    id_list READONLY
)
AS
BEGIN
    SELECT @param1 AS param1;

    -- join, filter, do whatever you want with this table 
    -- (other than modify it)
    SELECT *
    FROM @customer_list;
END;
GO

DECLARE @customer_list id_list;

INSERT INTO @customer_list (
    id
)
VALUES (1), (2), (3), (4), (5), (6), (7);

EXECUTE [dbo].[tvp_test]
      @param1 = 5
    , @customer_list = @customer_list
;
GO

DROP PROCEDURE dbo.tvp_test;
DROP TYPE id_list;
GO

The entire subject is discussed on the definitive article by Erland Sommarskog: "Arrays and List in SQL Server". Take your pick of which version to choose.

Summary, for pre SQL Server 2008 where TVPs trump the rest

  • CSV, split how you like (I generally use a Numbers table)
  • XML and parse (better with SQL Server 2005+)
  • Create a temporary table on the client

The article is worth reading anyway to see other techniques and thinking.

Edit: late answer for huge lists elsewhere: Passing array parameters to a stored procedure

Tags:

Sql Server

Sql Server 2008

Recent Posts