I've got a process that grabs a bunch of records (1000's) and operates on them, and when I'm done, I need to mark a large number of them as processed. I can indicate this with a big list of IDs. I'm trying to avoid the "updates in a loop" pattern, so I'd like to find a more efficient way to send this bag of ID's into a MS SQL Server 2008 stored proc.
Proposal #1 - Table Valued Parameters. I can define a table type w/ just an ID field and send in a table full of IDs to update.
Proposal #2 - XML parameter (varchar) with OPENXML() in proc body.
Proposal #3 - List parsing. I'd rather avoid this, if possible, as it seems unwieldy and error-prone.
Any preference among these, or any ideas I've missed?