How to send a big array to a stored procedure

Question

I need to send a several list (about 20000 id's) to a stored procedure, like this:

1391924, 6546510, 7419635, 6599910, 6546888, 1116510, 6546720, ...

I have this data on a List<int>

How can I do to send this list to an stored procedure?

And then I need to insert the id's list on a temporary table

Answer 1

You can use: Table-Valued Parameters

Table-valued parameters are a new parameter type in SQL Server 2008. Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

Table-valued parameters are like parameter arrays in OLE DB and ODBC, but offer more flexibility and closer integration with Transact-SQL. Table-valued parameters also have the benefit of being able to participate in set-based operations.

Eg.:

SQL Server:

Create Table-Valued Parameters:

  CREATE TYPE IDsTableType AS TABLE
  (                     
        [Product] [varchar](10) NOT NULL
  )

Pass it to Stored Procedure:

 CREATE PROCEDURE GetIDs
 (
       @TableVariable IDsTableType READONLY
 )
 AS
 BEGIN
  //Do Something

 END
 GO

C# Code for passing table valued parameter to Stored Procedure:

DataTable dataTable = GetData();
// Configure the SqlCommand and SqlParameter.
SqlCommand cmd= new SqlCommand(
    "GetIDs", connection);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = cmd.Parameters.AddWithValue(
    "@TableVariable", dataTable);
tvpParam.SqlDbType = SqlDbType.Structured;

Refer: Passing table-valued parameter data to a stored procedure

Answer 2

If you write the array into a DataTable, you can bulk insert the DataTable into the database. Then the stored proc could just read from that table.