> Well I searched the docs but I have not found how to start a
> transaction inside a stored procedure.
You can't. Transactions are a client side thing.
> CREATE PROCEDURE SP_FOO( aID Integer )
> begin
>
> SET TRANSACTION READ WRITE WAIT ISOLATION LEVEL READ COMMITED;
>
> DELETE FROM TABLE1 WHERE INT_ID = :aID;
> DELETE FROM TABLE2 WHERE INT_ID = :aID;
>
> COMMIT;
> END;
You are already in a transaction when a SP runs, so there would be no need
start a new one anyway.
> But I would like some more complex error handling. For example,
> SP_IMPORTMEMBERS_MNGR will call a few other stored procedure.
> Those called procedure will report success or failure. On failure,
> the *_MNGR procedure will execute a rollback and report the error to
> the application. On Success, a commit will be issued.
Return the result to the client app so _it_ can commit or rollback (or throw
an error in the SP itself)
If you are looking for more complex stuff that can't be fully handled client
side, take a look at SAVEPOINTS