> I want to have a generic Exception Handling in SP as Follows
>
> begin
>
> statements .... There may be any nested procs
>
> statements .... There may be any nested procs
>
> statements .... There may be any nested procs
>
> when any do
> begin
>
> here I would like to have exception name, description
> and nested proc name that has raised the exception
> which I would like to store in a table and show
> later when batch process is completed
>
> end
> end
>
> is it possible to do this with firebird ?
> if yes how can I achieve this ?

To get name of procedure that has raised exception you can use something like that

declare variable proc_name varchar(31);

> begin

proc_name = 'ALPHA';
execute procedure ALPHA;
proc_name = 'BETA';
execute procedure BETA;
...

> when any do
> begin

...

=====
It is not possible to get exception name inside stored procedure,
the only way is to "predict" possible error codes and list them separately:

begin
...
when sqlerror -123 do ...;
when sqlerror -456 do ...;
when gds_error blahblah do ...;
when exception my_exception do ...;
end

To get other unexpected errors use another extra block level

begin
begin
...
when sqlerror -123 do ...;
when sqlerror -456 do ...;
when gds_error blahblah do ...;
when exception my_exception do ...;
end
when any do ...;
end

=====
You can also handle errors for each command separately:

> begin
>
> begin
> statements .... There may be any nested procs
> when any do
> end
>
> begin
> statements .... There may be any nested procs
> when any do
> end
>
> begin
> statements .... There may be any nested procs
> when any do
> end
>
> end

=====
Another possibility is to trap/handle errors directly in "nested procs"
and return status as return parameter:

declare variable stat integer;
begin
execute procedure p1 returning_values :stat;
if (stat <> 0) then begin insert into logtable(procname, stat) values ('P1', :stat); end;
execute procedure p2 returning_values :stat;
if (stat <> 0) then begin .... end;
...
end


Ivan
http://www.volny.cz/iprenosil/interbase