What should I consider when deciding between passing a comma-delimited string to a stored procedure instead of calling it individually per record? I have to run the same stored procedure for a lot of records (hundreds or thousands, depending on user selection). The procedure is fairly basic: insert a record in two separate tables with some calculated values, and optionally update two columns on a 3rd table.

My application has a List&lt;T&gt; of records, and I'm trying to decide if I should loop through the list and run the procedure on each record, or combine the id values into a single string and make a single database call. If I make a single database call, then SQL would be responsible for converting the comma-delimited string to a temp table and running the scripts on all records at once.

I'm leaning towards passing a comma-delimited string of Ids to the procedure, but I am not sure if this is an ideal way of doing this kind of mass-update, or if I am missing some key factor that would affect this decision.

What should I consider when deciding if I should pass a comma-delimited string of ids to a stored procedure instead of calling it individually per record? Or is there a more efficient way to do this kind of update from an application?

I am using SQL Server 2005 and Entity Framework
Personally I would choose to pass a list of id's in as a table parameter to the stored procedure this would then allow you to do a set-based update instead of a row by row one which is less efficient.

I have never personally used the EF but a good artical on performing the above using ADO is below (ignore the fact it says it is for SQL 2008 as it also works on 2005). The same strategy would work better for you in this situation but you may need to adapt the implementation based on the fact you are using the Entity Framework.

http://www.mssqltips.com/sqlservertip/2112/table-value-parameters-in-sql-server-2008-and-net-c/

EDIT

As you rightly pointed out I am wrong about the fact this works on 2005 - sorry about that!

However, I have some alternate suggestions.

As SQL Server 2005 does support table variables (just not as parameters to stored procedures as you pointed out) you could parse the delimited string and insert the id's into a table variable. You could then use the table variable to perform a set-based update.

Alternatively the link below provides a different take on the same problem by persisting the values to a table first thereby avoiding serialization and de-serialization of the id values:

http://weblogs.sqlteam.com/jeffs/archive/2007/06/26/passing-an-array-or-table-parameter-to-a-stored-procedure.aspx

I hope this helps you.