Alan,

At 12:15 AM 9/11/2004 +1100, you wrote:

>Just as an aside - you either use SUSPEND or EXIT but not both.
>Use SUSPEND when you wish to return more than one value, EXIT when there is
>only one value to return.

That is incorrect. Use SUSPEND when the procedure is for returning
multiple row sets, i.e. a selectable stored procedure, and *only* for this
purpose; use EXIT when you want to pass flow of control directly to the
last END statement and *only* for this purpose. To break out of a looping
block, use the LEAVE statement.

When writing an executable procedure that returns values, the returned
values can be one or many: there is nothing that restricts them to a
single value. The values in the structure returned occur in the order they
are declared in the RETURNS clause.

Unhandled exceptions always cause execution to jump outward through the
nests of BEGIN --- END blocks looking for an exception handler following an
END statement. If no appropriate handler is found, execution stops,
control passes to the final END statement and the SQLCODE, GDSCODE and
error text are passed back in the error status vector. You do not get any
output in this case (nor if your SP handles an exception by re-raising it).

>> But I put only an example, my problem is that ibase_errmsg() allways
> returning empty string and I need to capture the last exception error.
>
> I can correct a prior example, try :
> if (:ncount > 0 ) then
> begin
> result_value=0;
> suspend;

Your problem here is the misuse of SUSPEND. Because there is no loop-wait
cycle in your execution logic, SUSPEND behaves exactly like EXIT and passes
execution directly to the last END statement.

> EXCEPTION ERROR_SP_GENERIC 'You cant delete existing tickets
> dependences';
> EXIT;

The EXCEPTION and EXIT statements never get executed, because execution
immediately jumps to here:.

> end

Here's what you need to do:
BEGIN
BLAH;
BLAH;
if (ncount > 0 ) then /* remove the colon from :ncount !!)
EXCEPTION ERROR_SP_GENERIC 'You cant delete existing tickets
dependencies';
BLAH...;
BLAH....;
result_value = your_good_result;
END

Helen