How to send a large array to a stored procedure
I need to send multiple lists (about 20,000 IDs) to a stored procedure, for example:
1391924, 6546510, 7419635, 6599910, 6546888, 1116510, 6546720, ...
I have this data on List<int>
How do I go about posting this list to a stored procedure?
And then I need to insert a list of ids into a temporary table
source to share
You can use: Tabular parameters
Table-valued parameters are a new parameter type in SQL Server 2008. Table-valued parameters are declared using custom table types. You can use table parameters to send multiple rows of data to a Transact-SQL statement or subroutine, such as a stored procedure or function, without creating a temporary table or multiple parameters.
Table parameters are similar to parameter arrays in OLE DB and ODBC, but they provide more flexibility and tighter integration with Transact-SQL. Tabular parameters can also be useful for participating in set-based operations.
For example:.
SQL Server:
Creating table parameters:
CREATE TYPE IDsTableType AS TABLE
(
[Product] [varchar](10) NOT NULL
)
Go to the stored procedure:
CREATE PROCEDURE GetIDs
(
@TableVariable IDsTableType READONLY
)
AS
BEGIN
//Do Something
END
GO
C # code to pass table parameter value 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;
source to share
If you write an array to a DataTable, you can insert an array of DataTable data into the database. The stored procedure can then simply be read from this table.
source to share