PMFJI..

My update procs reference all fields. But, there are cases where I may only reference certain fields. The key is to have a toolset that builds and manages the sp's for you...

>Bonnie,
>
>I am curious how you strucutre your UPDATE sp? Do you just send ALL the values of all the fields, or do you just send the values of the fields that have changed data? If the former, does your SP create dynamic SQL to do the update?
>
>I basically ask because I am wondering how this would work with trigger based audits which use IF UPDATED()...
>
>BOb
>
>
>>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?