> 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?).

No,

Stored Procedures in Firebird are just like a query. Other DBMS treat
stored procedures more like scripts without a transaction context. In
Firebird however, Stored Procedures run in the context of the
transaction they are inside.

So if your transaction looks something like

insert into tablea values (1,2);
execute procedure sp_dosomething;
execute procedure sp_dosomethingelse;

and then you rollback, EVERYTHING is rolled back, including
everything done inside both stored procedures.

> 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?

If that was how it worked, it would definately be an inconsistent
state. Fortunately for all of us it doesn't work that way.

If there is an exception raised within a stored procedure that is not
internally handled by the stored procedure, the exception is then
returned to the client application. The client application can then
decide whether to rollback the transaction entirely, to rollback the
transaction to a savepoint (FB1.5 or +), or whether to take some
other action.

A call to a stored procedure is considered an atomic operation.

Imagine

insert into tablea (name, id) values ('adam',5);

caused a primary key violation. Because the insert operation is
atomic, it treats it just like it was never run.

So

execute procedure AddPatient(etc);

is also atomic. If there is an exception that isn't internally
swallowed within AddPatient, then the operation failed, and it is
just like it was never run, and the exception is returned to the
client.

In your case, if stored procedure entity raises an exception, and
stored procedure person doesn't swallow it, then the exception is
returned to stored procedure AddPatient. If AddPatient doesn't
swallow it, then the AddPatient operation fails and the exception is
returned to the client where your client application would most
likely display or log a meaningful error message.

Adam