Thanks Fernando,
This is exactly what I need. I'm not versed on Firebird PSQL so I took the first idea that looked to be working. Both of your replies were right on the spot. Thanks, again.

Also thanks Helen,
my code was far from definitive, more like concept describing.
You are great.

--- On Mon, 12/21/09, Fernando Medeiros <fdomds@...> wrote:

From: Fernando Medeiros <fdomds@...>
Subject: Re: [firebird-support] selectable SP, exception handling
To: firebird-support@yahoogroups.com
Date: Monday, December 21, 2009, 2:55 PM







 









sorry if i dont get right but instead of generating exceptions, because they

do not avoid ?

------------ --------- --------- ----

create procedure trim_string

returns ( cod integer, nr varchar(50), trim_numeric integer )

as

begin

for select cod, nr from YourTable into :cod, :nr do

begin

select trim_numeric from sp_aux(:nr) into :trim_numeric;

suspend;

end

end

------------ --------- --------- -------

CREATE PROCEDURE SP_AUX(

NR VARCHAR(50))

RETURNS (

TRIM_NUMERIC INTEGER)

AS

declare variable s char(1);

declare variable str varchar(50);

declare variable i integer;

begin

i = 1;

str = '';

while (i <= character_length( nr)) do

begin

s = substring(nr from i for 1);

if (position(s, '0123456789' )>0) then str = str || s;

i = i + 1;

end

trim_numeric = cast(str as integer);

suspend;

end

------------ --------- --------- -------

i dont know if ths help you. : )



[]s



2009/12/21 Helen Borrie <helebor@iinet. net.au>



> At 08:49 AM 22/12/2009, you wrote:

>

> > num = CAST('355a' as integer);

> >

> > IF (num > 0)

> > then NR_NUM = num;

> > else //repeat, whatever this needs more work

> >

> > when any do begin

> > exception ;

> > end

> >

> > suspend; // this is where it stumbles

> > end

> >end

> >

> >The problem is that the parser says that suspend is "invalid token".

>

> The parser is expecting the name of an exception that you have previously

> defined using CREATE EXCEPTION.

>

> However, if I understand correctly what you want to do (skip over any

> record that throws an exception), then throwing another exception won't get

> you out of jail. If you want your exception handler to pick up all

> exceptions and discard them, provide an "empty" handler, e.g.

>

> when any do begin

> -- do nothing

> end

>

> After an exception has been handled this way, execution returns to the

> start of the same begin...end block in which the exception occurred.

> Unhandled exceptions simply drop through to the last 'end' statement and

> return control to the caller. So design your looping blocks and place your

> exception handling blocks so that execution will return to the right place

> and keep executing.

>

> Usually, you would only want to throw a specific exception where you want

> specific handling, e.g.,

>

> for....do

> begin

> ...

> if (some condition) then

> exception MyException;

> .... (do more stuff)

> suspend; -- only happens if no exception occurred

> end

> when exception MyException do

> begin

> .... (do something such as write to a log)

> end

> when any do -- now deal with any other exceptions

> begin

> -- do nothing (swallow the exception and move on to the next

> iteration)

> end

>

> end

>

> Don't take these examples as a definitive tutorial, "Everything you need to

> know about PSQL logic"! But they might help you to see how exception

> handling can be used to create "savepoint logic" inside your modules.

>

> Also, be aware that advancing versions of Firebird have provided

> enhancements to exception handling logic. Particularly, look in the PSQL

> section of Firebird release notes, starting with the Fb 1.5.6 notes and

> tracking through to the Fb 2.x notes. Better still, the Language Reference

> Update guides have them nicely potted up for you, with examples.

>

> ./heLen

>

>

>

> ------------ --------- --------- ------

>

> ++++++++++++ +++++++++ +++++++++ +++++++++ +++++++++ +++++++++ +++++++++

>

> Visit http://www.firebird sql.org and click the Resources item

> on the main (top) menu. Try Knowledgebase and FAQ links !

>

> Also search the knowledgebases at http://www.ibphoeni x.com

>

> ++++++++++++ +++++++++ +++++++++ +++++++++ +++++++++ +++++++++ +++++++++

> Yahoo! Groups Links

>

>

>

>



--

http://twitter. com/fernandomds

http://fernandomede iros.com. br/blog



[Non-text portions of this message have been removed]

























[Non-text portions of this message have been removed]