"You will see new SQL programmers try to pass arguments as XML or as CSV (Comma Separated Values) list strings. They have added the overhead of a parser (the XML parser or whatever code they wrote for splitting up the CSV string) and endangered their data integrity. SQL Server can handle over 2,000 arguments, which is more than enough power for any realistic situation."

pschmidt 87697 (8/25/2010)

---

For instance, if you have a web based email system that lists emails gives the user the option to select the emails they want to delete and then click a delete button, you can simply pass in one call the all the ids of the emails from the UI in a delimited string to one stored proc. Example: "proc_delete_emails @userid = 1, @email_ids = '2,5,8,9,23,46,57,89,99,100,110,112,113,115,117,119,122,124,127,129'"

The rules of thumb for T-SQL are not to write any procedure over 50 lines (one page) and not to use PRINT.

The rules of thumb for T-SQL are not to write any procedure over 50 lines (one page) and not to use PRINT.

DEK46656 (8/25/2010)

---

I would suggest that the selection of emails be stored as a set, into some form of staging table containing the email ID and the user / session. Then the stored procedure would be called, passing as a parameter the user / session. The procedure would then execute the delete on the emails associated to that user.

I've used CSV in the past, but have been moving away from it. I've been trying to improve my data structures and code. CSV can be considered "against the rules" as defined by Codd (Rule 1 I believe).

Mike Dougherty-384281 (8/25/2010)

---

I'm not using a language that abstracts away the heavy lifting of the conversation with the SQL server, so it makes no difference to me whether I construct a command batch with 25 singleton operations or build a 25 id CSV - the client/app iteration is the same. The batch gets submitted in one step either way. I like the DIY approach but your tools (language, paradigm, etc.) might not provide the same flexibility.