clementdoss wrote:

>Hi,
>
>Well I searched the docs but I have not found how to start a
>transaction inside a stored procedure.
>
>For example:
>
>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;
>
>Is raising a syntax error. Unknow token: SET
>
>
>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.
>
>SP_IMPORTMEMBERS_MNGR
> -Start Transaction
> -Result = import partner input file -- This is a stored procedure
> -if result<>0 then Goto SomeError
> -Result = Validate Partner File -- This is a stored procedure
> - if Result<>0 then Goto SomeError
>
> - Commit
> Return 0 -- Here I must exit the procedure
>
> -:Somerror
> Rollback
> Return Result
>
>end;
>
>
>How can I code this in Firebird?
>
>
>TIA,
>Clément
>
>

Clément,

You cannot start a transction inside your sp.

The transaction runs inside the transaction that was already started,
when the SP runs.

Take a look on Savepoints, on FB 1.5 release notes, maybe it is what you
are after.

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br