> Adam,
> > Stored procedures are a good way to abstract the table design from
> > the function. For example, you could create a procedure
> > SP_Create_Customer and pass into it all the information required to
> > create a customer. Internally it may run 5 or 6 queries to actually
> > add a customer.
> I have a question regarding this practice. Imagine I have a Stored
> Procedure that internall runs other stored procedure. So,
>
> Stored Procedure AddPatient Runs
> Stored Procedure Person which runs
> Stored PRocedure Entity
> Each stored procedure is design to run the other. Here, I can
> effectively reuse procedures - so, an SP to add Doctor will also run
> SP PErson which in effect will run SP_Entity.
> My question is, if I run these as SQL queries from the application, I
> can run them inside a transaction - and, rollback in the event of an
> error.
> However, I have no control over SP (right?). So, this SP may run few
> of the internal SP and insert some records - then there is an error -
> there is no mechanism for rollback - right? Will this not leave the Db
> in an inconsistent state?
> MS

they all get rolledback if you roll the calling transaction back. They all
run in the context of the same transaction.
Alan