How can I send a list(s) of data into a stored procedure for processing?
For instance, I have a table: GroupContacts(groupname, userid, contactid).
At times I will be inserting X amount of records into it, X depends on how many contacts need to be added to a group by a user.
How can I send a list of (groupname, userid, contactid)'s into a stored procedure and then use some kind of for-loop to iterate through the list and insert the records?
I will be calling the stored procedure from an asp.net application so I can't create a table on the fly. I need a stored procedure that will accept a list of entries that will be inserted into an existing table. The list can be of unknown length (ie. each time it could be a different size)
So create a table as part of your schema. When you have to support front-end code that behaves that way (needing to pass variable arrays of data), you need to either:
1) Fix the front-end code
2) Build an N-tier layer to support the existing front-end, or
3) Modify your database structure to support it.
Applications that try to send varying amounts or unstructured data in a single call need some kind of buffer between them and the database. These front ends are usually old code based on a simpler application model (where the tiers aren't well defined, and sometime aren't defined at all). This is often why the old application is being replaced!
If you get the database right, it is hard to get the application wrong. If you get the database wrong, there is very little chance of getting the application right. The database is actually the key to application performance, if the database interface is clean and quick, the application can scale. When the database needs to do XML-like processing of input, it almost can't be quick and clean.