Thanks Helen,

That was good info and very helpfull.

>Yes, Part Seven (5 chapters) of The Firebird Book.

Didn't realize there was a book. That will probably be the way to go.
Off to Barnes & Noble tomorrow to order it.

Regards,
Roy F.

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 02:01 AM 1/03/2005 +0000, you wrote:
>
>
> >Hi Helen,
> >
> > >Firstly, the query is an inner join that finds
> > >no match between the two tables - hence the DSQL
> > >query returns no rows.
> > >Inside the SP. the query also returns no data to
> > >the variables - hence the uninitialised) variables
> > >simply return their unchanged values (nulls) as
> > >return values to the SP call.
> >
> >Thanks for that explanation. It makes sense now. I tried changing

the

> >SP like Dimitry recommended but it still returns the null

variables.

> >So the question now is, is there anyway for the SP to not return
> >anything if the query inside doesn't? Would it be better to just

check

> >to see if the value of the variable from the SP is null rather than
> >checking if it returned a record?
>
> Yes, you can do this with an exception.
>
> create exception EMPTY_SET 'There are no rows in this set';
> commit;
>
> create procedure get_stuff (akey integer)
> returns (
> <list of vars>
> as
> begin
> if (not exists (select ..... from ...where id - :aKey)) then
> exception EMPTY_SET;
> else
> -- carry on
> end
>
>
> >I'm also a little unclear on some aspects of Firebird SP's. How do

you

> >know when to use the FOR....DO as Dimitry suggested,
>
> Use the FOR SELECT...DO where you want to iterate through a

mutli-row

> set. The SELECT ... INTO syntax will except if the query returns

multiple

> rows.
>
> >what about
> >SUSPEND
>
> Use SUSPEND only when you plan to call the SP using SELECT. SUSPEND
> literally suspends execution of the procedure and waits for the

client to

> fetch the latest output row from the buffer.
>
> In an executable SP, SUSPEND simply causes an immediate EXIT.
>
> > vs EXIT, etc?
>
> >Is there any good detailed documentation about
> >SP's in Firebird?
>
> Yes, Part Seven (5 chapters) of The Firebird Book. You can find

some

> documentation in the IB 6 Beta manuals: a little in LangRef.pdf and

more

> in DataDef.pdf and EmbedSQL.pdf.
>
> Also do both a site search and a Knowledgebase search at

www.ibphoenix.com

> - you will probably find some papers on the topic.
>
> ./heLen