>You're saying that every updated record requires a round-trip call, which will result in consuming the network resources.
That's correct. IMO, you would be better off making one trip. You can encode the changes in an XML document and send it to the stored procedure. The stored procedure and parse and shred the XML document using the OPENXML() rowset function. It works very well.
Although XML is not the only answer. When you send something to SQL Server, you're actually sending a batch of commands. Most people tend to make this batch contains a single statement but there's nothing that says you can't submit multiple statements in a single call. You'll have plenty of room with a max. batch size of 65K * network packet size <s>
>Still I don't see the advantages of using SPs.
Stored procedures offer a number of benefits:
- share rules and processes between applications
- provide another layer of abstraction
- provide a performance improvement
- act as a security mechanism
- provide a place to implement complex rules or processes that require more access to data.
- Reduce bandwidth usage
- ...
-Mike