You're saying that every updated record requires a round-trip call, which will result in consuming the network resources.

Another issue, suppose a network failure took place during the updating process, I think you will end up with saving the data partially.

Still I don't see the advantages of using SPs.



>Nabil,
>
>I update records one at a time, our SP's only handle one at a time. However, there is another way to do it (in SQL 2000) and that is to use SQL's new XML capabilities. I haven't even played around with this yet, so I don't really know much about how to use XML in SQL, other than what I've read. But, there's a function called OPENXML() that allows you to insert records into SQL from an XML file. Look it up in the BOL. Or maybe Mike Levy might pop in here with a more definitive answer ... he's the SQL guru. <s>
>
>~~Bonnie
>
>
>>I liked the idea of using VFP.
>>One more doubt, How did you tackle the updating/inserting of multi records in SPs?
>>
>>
>>>Nabil,
>>>
>>>Well, some may say it's overkill, but for this latest app that we are working on, we have *lots* of SPs. This is for a SQL Server database. We anticipate having our database set up to be accessible through SPs only once the app is finished and deployed. Of course, right now in development, we can still access the tables directly. But, in the app, all access both to and from the database is *only* through SPs. We have, at minimum 3 SPs for every table ... select/delete/update(add). Of course, some tables will have more than that, because there will be many ways that we will need to access the data for things like lookups and parent/child relationships. But, it's still *all* done with SP's.
>>>
>>>Right now, we have 300 SPs ... and we only started this project a few months ago ... there will be plenty more SPs to be added still as we get further into it.
>>>
>>>If you've read some of my other posts, you may remember me mentioning that we automate a lot of our stuff (using VFP) and generating SPs is just one more thing that we automate, so creating parameters for every column is no big deal.
>>>
>>>
>>>~~Bonnie
>>>
>>>
>>>>I noticed that you always mention STORED PROCEDURE when talking about data manipulation. Can you explore more on this issue?
>>>>Create one SP per project?
>>>>Create one SP per table?
>>>>Create one SP per table & type of operation (Select/Insert/update/delete)?
>>>>Do you create a parameter for every field?
>>>>What about if the table contains a lot of fields?
>>>>How practical do you think it is using SP?
>>>>
>>>>
>>>>>Nabil,
>>>>>
>>>>>That will work, however I wouldn't do it that way because of the fact that it requires two round-trip calls to the server. Instead, either send both queries at the same time (your Sql string will contain both queries in it) or call a Stored Procedure that returns both tables (that is the way that I would do it).
>>>>>
>>>>>~~Bonnie
>>>>>
>>>>>
>>>>>>I created a typed dataset which consist of a parent table,child table, and one to many relation.
>>>>>>
>>>>>>The mechanism I'm following to retrieve the data of both tables is:
>>>>>>1- Assign the parent's parameters values.
>>>>>>2- Fill parent table.
>>>>>>3- Assign the child's parameters values.
>>>>>>4- Fill child table.
>>>>>>
>>>>>>Is that ok, or there is a better approach?