Passing array parameters to a stored procedure

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?

The best ever articles on this matter are by Erland Sommarskog:

• Arrays and Lists in SQL Server 2008 - Using Table-Valued Parameters

• Arrays and Lists in SQL Server 2005 and Beyond- When TVPs Do Not Cut it

• Arrays and Lists in SQL Server 2000 and Earlier

He covers all options and explains pretty well.

Sorry for the shortness of the answer, but Erland's article on Arrays is like Joe Celko's books on trees and other SQL treats :)

StackExchange Database Administrators Q#629, answer score: 53