Hi Bonnie,

> most DBA's would agree with that

Strangely not the Oracle admins I had to work with. *g*

> I'm not sure what you mean though by "called once with all parameters required"

I wouldn't either reading my message a few hours later. *s* What I meant is that the number of calls you make must be the same for all backends. If you originally write the code for MS SQL server as a single stored procedure call, it must be a single procedure call for other databases, as well. You cannot make one call, process the result on the client side and make another call to the database. This would be relevant when the new database is less powerful than the one you originally designed the app for and does not support the entire process on the database side.

> Other than the Stored Proc that tells how to retrieve data.

Which can be quite difficult. Not all ODBC drivers support returning a result set from a stored procedure. Oracle doesn't with their ODBC drivers.

> And as far as saving the data, we have what we call "basic" stored procs that contain nothing but an INSERT or UPDATE of one table

basic saving the data is not what I'm talking about. Those commands are standardized and don't cause any significant problems even with just one data layer. I was thinking of compound or more complex operations that are implemented entirely different in various backends. For instance, updating a record or inserting one if it doesn't exist. Many databases have a command for that, "INSERT OR REPLACE", "UPSERT", "MERGE". Not so MS SQL where you need a sequence of INSERT followed by UPDATE.

Sometimes you need a mixture of client side processing and server side processing (printing thousands of balances as of now and updating them). You can't run one statement against the database for each balance, because that won't meet the condition of "as of now". At the same time getting all balances, then printing them all, then updating them all, is often not the most efficient way to deal with this for a particular back end and might keep records locked for too long.