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