Technique for sending lots of data into stored proc
I frequently come across the situation where I want to pass an array of Ids as a parameter to a stored procedure.
For example with GetCustomerAccounts stored procedure, the client chooses say 20 customers from a list. I want to then pass these 20 customer Ids to the procedure for it to return their accounts.
Currently I solve this by having a Parameters table. I populate this with my customer Ids and a single Guid ID. This single Guid ID is then passed to the procedure which then joins agains the Parameters table. I don't like this as it involved multiple trips to the database.
Another possibility would be to pass the Ids in a comma separated Varchar parameter. The stored procedure could then parse this string into a temporary table and then join against that. Again that feels pretty nasty.
Is there a better way to do this?