Hi,

I was wondering if anyone could provide a little guidance to the best
approach to this:

In my database I generally creat an insert/update procedure for every table
(plus a few others).

Say that I have an operation that requires an insert into table X, Y and Z -
what is the best approach to these inserts?

1. Execute the three insert procedures as three distinct operations in the
client.
2. Exeucte the three inserts as statements bunched into a single new
procedure.
3. Execute the three insert procedures by creating a single procedure to
call the three insert procedures (and handle the output, etc) and executing
that?

My apologies if this question is a little vague and I appreciate that the
answer could vary depending on my circumstances but I often encounter this
issue and I've never really found a suitable answer.

Any and all advice is gratefully recieved.

Kind thanks

Chris.

Chris Strug wrote:
Show quoteHide quotewell yes, it is kinda hard to be specific when the problem is this
vague.
What happens if insert into Y fails? should the insert into Z be done
anyway? Would the insert into X still be valid?
If the data being inserted into X, Y, and Z are independent, then maybe
have three seperate procedures would be simplest and easiest.
If any failure to insert invalidates all the other inserts, you might
be better off using transactions. Begin the transaction before
inserting to X, and then rollback if any of the inserts fail, and
commit if all succeed.
hard to say much more without mroe details.

User defined functions and booleans?
BCP .dbf file useing t-sql
tough request
How to combine several long strings and then insert them to a text field
Probably very trivial SQL Query question....