The problem seems to be at this line:-
BEN_ID = GEN_ID( BENID_GEN, 1 );

This is the full error description

Dynamic SQL Error
SQL error code = -104
Unexpected end of command
Statement: ALTER PROCEDURE INSERT_BEN ( BENEF_ID VARCHAR(15)
,BEN_INITIALS VARCHAR(5)
,BEN_FIRSTNAME VARCHAR(30)
,BEN_SURNAME VARCHAR(30)
,BEN_TITLEID SMALLINT)
RETURNS (BEN_ID INTEGER)
AS
BEGIN
IF (BENEF_ID <> '') THEN
BEGIN
SELECT BENID
FROM BENEFICIARY
WHERE (ID = :BENEF_ID)
INTO :BEN_ID

FULL SP:-

set term ^ ;
CREATE PROCEDURE INSERT_BEN AS BEGIN EXIT; END ^

SET TERM ; ^
ALTER PROCEDURE INSERT_BEN ( BENEF_ID VARCHAR(15)
,BEN_INITIALS VARCHAR(5)
,BEN_FIRSTNAME VARCHAR(30)
,BEN_SURNAME VARCHAR(30)
,BEN_TITLEID SMALLINT)

RETURNS (BEN_ID INTEGER)
AS

BEGIN
IF (BENEF_ID <> '') THEN
BEGIN

SELECT BENID
FROM BENEFICIARY
WHERE (ID = :BENEF_ID)
INTO :BEN_ID;

IF (BEN_ID = NULL) THEN
BEN_ID = GEN_ID( BENID_GEN, 1 ) ;


INSERT INTO BENEFICIARY ( BENID
, ID
, INITIALS
, SURNAME
, FIRSTNAME
, TITLEID)

VALUES ( :BEN_ID
, :BENEF_ID
, :BEN_INITIALS
, :BEN_SURNAME
, :BEN_FIRSTNAME
, :BEN_TITLEID);



END
END
^
SET AUTODDL ON;
SET TERM ; ^
COMMIT WORK;



I want to only generate a number if the beneficiary with a particular id was
not found. The idea is to link the found record(if found, naturally) to a
member.

Thanks,
Dion.


-----Original Message-----
From: Alexander Khvastunov [mailto:ak@...]
Sent: Wednesday, January 16, 2002 5:47 AM
To: IBObjects@yahoogroups.com
Subject: Re: [IBO] SP????

> Now I get :-
>
> Dynamic SQL Error
> SQL error code = -104
> Unexpected end of command
> When running the following script:-
>
> COMMIT WORK;
> SET AUTODDL OFF;
> SET TERM ^ ;
>
> CREATE PROCEDURE INSERT_BEN AS BEGIN EXIT; END ^
>
> SET TERM ; ^
> ALTER PROCEDURE INSERT_BEN ( BENEF_ID VARCHAR(15)
> ,BEN_INITIALS VARCHAR(5)
> ,BEN_FIRSTNAME VARCHAR(30)
> ,BEN_SURNAME VARCHAR(30)
> ,BEN_TITLEID SMALLINT)
>
> RETURNS (BEN_ID INTEGER)
> AS
> Declare Variable bencount integer;
> BEGIN
> IF (BENEF_ID <> '') THEN
> BEGIN
>
> SELECT count(BENID)
> FROM BENEFICIARY
> WHERE (ID = :BENEF_ID)
> into :bencount;
>
> IF ( BenCount >= 1 ) THEN
> BEN_ID = GEN_ID( BENID_GEN, 1 ); <-----------!!!!!!!!!!!!!!!!!

You forgot about the semicolon here.

Anyway your procedure looks some wrong...
Use EXISTS instead of counting records:

ALTER PROCEDURE INSERT_BEN ( BENEF_ID VARCHAR(15)
,BEN_INITIALS VARCHAR(5)
,BEN_FIRSTNAME VARCHAR(30)
,BEN_SURNAME VARCHAR(30)
,BEN_TITLEID SMALLINT)

RETURNS (BEN_ID INTEGER)
AS
BEGIN
IF (BENEF_ID <> '') THEN
BEGIN

If (not (exists (select benid from beneficiary where id = :benef_id)))
Then
begin
BEN_ID = GEN_ID( BENID_GEN, 1 );

INSERT INTO BENEFICIARY ( BENID
, ID
, INITIALS
, SURNAME
, FIRSTNAME
, TITLEID)

VALUES ( :BEN_ID
, :BENEF_ID
, :BEN_INITIALS
, :BEN_SURNAME
, :BEN_FIRSTNAME
, :BEN_TITLEID);
end
END
END



Best regards,
Alexander Khvastunov

IBExpert - The Most Expert for InterBase and FireBird
http://www.ibexpert.com



___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/