At 05:25 AM 19/06/2003 +0000, you wrote:

>Hi tim,
>
>Thanks for that and the quick response.
>the stored procedure worked (you'll be amazed to know :) )
>although i did find the syntax a little odd.
>
>I am using EMS interbase/firebird manager to connect to the database.
>I tried running the stored procedure in the sql editor by typing
>
>EXECUTE FIRSTPROCEDURE
>
>but it gave me an error. any clues??

Yup. "Multiple rows in singleton select" or something similar?
Don't use EXECUTE on a "selectable stored procedure" - that's the kind Tim
and I showed you. Use SELECT for any SP that outputs more than one
row. If Quickdesk has a way to show you return parameters, you could test
the syntax for singleton output if your table had a primary key. Let's say
that, instead of loading 1 into your Field1, you'd used a generator and a
trigger to get a unique number (pretty limited with SmallInt, yeah!):

CREATE PROCEDURE SecondProcedure
returns FirstName varchar(50)
AS
BEGIN
select FirstName from TBLMARTIN
where Field1 = 2
into :FirstName;
END

You don't need a suspend here because there's only one row. You can call
EXECUTE on this without getting an error. (You can also use SELECT on it
if you like, since it returns something). The result will be returned to
the client in the XSQLDA structure. EMS would show you this in some kind
of "Return values" display, I suppose.

In practice, we use "executable procedures" to perform DML that doesn't
return output, and "selectable procedures" when the SP is designed to
return a result set.

heLen