At 03:06 PM 11-10-02 -0700, you wrote:

>hi,
>
>i've created a store procedure.
>the idea is to raise an error code/message to the user if the procedure
>failed and to raise a message if the procedure succeed.
>i've read the language reference manual, and it said that if used, WHEN must
>be the last statement in the begin..end block.
>
>i guess i have to assign one variable. but how do this variable know that
>this procedure works well or not.
>fyi, i need to rollback everything if there was an error, if not, then
>commit.

The first thing to understand is that you CAN'T rollback the transaction
from within the stored procedure. The transaction is started by the client
and it must be the client which ends it also.

Next, in your SP you don't have to perform any "undoing" of the work of the
SP. Nothing gets committed until the transaction is committed by your
client app; and if your client rolls back the transaction, then every
piece of work performed during that transaction gets rolled back.

It's fine to create a custom exception called 'failed' and have it override
any exception that occurs. Let's presume you created it something like this:

CREATE EXCEPTION failed 'Sorry, that did not work';
COMMIT;

Then, in your SP, do this:

WHEN ANY DO
EXCEPTION failed;

That's all there is to it. When any exception occurs, the application will
receive the message 'Sorry, that did not work'. Just have your app trap
that message and perform a rollback and you will be where you want to be...

Oh, and remove that SUSPEND statement. It's not appropriate to a SP that
does not return a dataset: in an executable SP it is equivalent to EXIT.

heLen