Best way to handle a list that is used as a parameter to a stored procedure
I have a list of item ids (12345,23456, etc.), but there are about 7000 of them. What I do is take each item in the list and pass them to a stored procedure to get a different number if it matches the criteria in the procedure. Here is the C # code:
cmd.Parameters.Add("@itemId", SqlDbType.NVarChar);
cmd.Parameters.Add("@division", SqlDbType.NVarChar);
foreach (var item in itemNumber)
{
cmd.Parameters["@itemId"].Value = item;
cmd.Parameters["@division"].Value = CboRequestDivisions.SelectedValue;
reader = cmd.ExecuteReader();
while (reader.Read())
{
CboProductionOrder.Items.Add(reader[0].ToString());
}
reader.Close();
}
and stores the stored procedure:
Select replace(PRODID,'Prod-','') from PRODTABLE where itemID = @itemId and (PRODSTATUS ='0' or PRODSTATUS = '2' or PRODSTATUS = '3' or PRODSTATUS = '4') and Dimension2_ = @division order by PRODID asc
Calling this process to search for 7000 results at the same time is ludicrous. Is there a way to spill each element so I call the procedure multiple times in a row Right now, it takes about 20 minutes to complete the items.
source to share
I would suggest creating one parameter with all delimited items and then using PARSE in SQL to work with them together.
Another way to do this is to treat the parameter with the full collection of items as varchar and build a dynamic query, and then execute it with the exec command.
source to share