Sample Header Ad - 728x90

Passing array parameters to a stored procedure

70 votes
11 answers
325984 views
I've got a process that grabs a bunch of records (thousands) and operates on them. 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 pulling the IDs down along with "payload" data via another stored proc. I don't need to update all that data, though -- just update a flag on certain records. 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 IDs into a 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?
Asked by D. Lambert (1067 rep)
Jan 14, 2011, 07:05 PM
Last activity: Feb 5, 2025, 04:32 AM