At 10:28 PM 23/07/2005 +0000, you wrote:

>I've been writing SPs for MSSQL T-SQL for many years but I'm new to
>Firebird and SQL99 stuff. Here's a simple (I thought) SP that won't
>compile..
>
>CREATE PROCEDURE SP_PLAYERFIND ( PLAYERNAME VARCHAR(16) )
>RETURNS ( PLAYERKEY NUMERIC(15,0) )
>AS
>BEGIN
>SELECT PKEY FROM PLAYERS WHERE PLAYERNAME=:PLAYERNAME
>INTO :PLAYERKEY;
>SUSPEND;
>END
>
>The error returned by IBEasy SQL tool is "Unexpected end of command".
>
>I've tried many different ways of entering this in various
>upper/lower case, with/without semi-colons, with/without "suspend"
>combinations. I always get an error when I try to compile it.
>
>I'd -greatly- appreciate your expert advice.

The CREATE PROCEDURE code, per se, looks OK, although it is likely to throw
a run-time exception ("Multiple rows in singleton select") if the table
contains more than one occurrence of the same PLAYERNAME. You should use a
FOR SELECT structure for this query and - of course! use it as a
selectable SP.

You should look for some attribute in IBEasy that stops the parser from
treating the variable references (e.g. :PLAYERNAME) as parameters in a DML
statement. For example, in tools built with Delphi and C++Builder, there
is usually a checkbox labelled "ParamCheck" or something similar. This
should be false if you are submitting a PSQL source statement dynamically.

Do you know whether IBEasy is actually capable of processing a CREATE
PROCEDURE statement dynamically? Perhaps you need to run a script, with
the full SET TERM syntax, and use a terminator on the final END statement,
as you would in ISQL.

./heLen