At 08:58 PM 23/07/2005 -0400, you wrote:

>That works! Thank you Doug!
> So if I have multiple input params in the SP it might look like...
> SELECT * FROM SP_PLAYERFIND('ProfKill','Blah',34,0)
> Just keep the param values in the same order as they are in the SP?

Yes. Essential. The XSQLDA structure that carries the parameters across
the API comprises one XSQLVAR structure for each input argument specified
in the SP header, in strict order and basic data type, as defined. There
is no run-time compiling as in TSQL - your SP definitions are compiled at
create time. The binary code survives everything except an ALTER, RECREATE
or CREATE OR REPLACE operation, i.e. the binary code is stored in backups
and is not recompiled by a restore.

> Here's a question, how would I call a SP from code that does an Update or
>an Insert?

You would write an EXECUTABLE stored procedure to do the job (no SUSPEND
calls!!) and EXECUTE the procedure with

EXECUTE PROCEDURE MyProc(MyParam1, MyParam2,....)

> You know what, this is all soooooooooo different from TSQL in MSSQL Server.

There are are LOT of differences. TSQL was tacked onto Sybase and touted
as a "stored procedure language". It's more like "intelligent scripting"
than a purpose-designed structured programming language like Firebird's
PSQL or Oracle's P/L. Not that PSQL and Oracle P/L are in any sense
directly interchangeable at code level. However, there is a rather
advanced product available, called Fyracle, that bridges the divide between
P/L and PSQL, making some important P/L features available to a Firebird
back-end.

In Firebird you don't need to create temp tables for the output of SPs
intended to return multi-row output sets. The selectable stored procedure
(using the appropriate coding structures) outputs rows one by one directly
to the client, in response to the calling application's Fetch calls. As
such, it behaves something like a "programmable view" (although there is no
such thing!!) Do take note of my comment about your syntax in the example
you first asked about, or it will jump up and bite you.

For "converting" your TSQL, reduce the logic right down to pseudocode and
begin from the beginning. PSQL a simple language with a lot of power and
it's not at all like TSQL or VBScript.

It looks as if you might need to do some study about PSQL. Ironically, the
most useful published *free* information about the syntax can be found in
the EmbedSQL.pdf volume of the IB 6 beta docs. The reason it is "ironic"
is that ESQL isn't the usual environment in which SPs are used
widely. (Triggers, also written in PSQL, are equally applicable in ESQL
and dynamic systems, of course!) You can pad that info up by getting hold
of various papers available from the IBPhoenix website. Need I say it, you
could alternatively get a copy of The Firebird Book and study Part Seven (5
chapters).

./heLen