At 07:20 PM 23/11/2007, you wrote:

>Hi there,
>
>I´m just starting with firebird and wondering how to achieve the
>following:
>I´m doing a select to find out if a record for a given parameter
>exists. If not, I´d like to insert a new record, else I´d like to
>update one field of the existing record (which is an integer -> this
>integer needs an increment +1).
>
>I do have some code (SPROC) but I don´t get it working. Please
>hint me something:
>
>ALTER PROCEDURE SP_TBLINDEX_INS_UPD
>(
> SEARCHTERM VARCHAR(400) CHARACTER SET UTF8 DEFAULT NULL
>)
>RETURNS
>(
> SEARCHEXISTS SMALLINT
>)
>AS
>BEGIN
> select ID from TBLONE where searchterm = :SEARCHTERM into :SEARCHEXISTS;
> SUSPEND;
> if (:SEARCHEXISTS = 0) THEN
> insert into TBLONE (SEARCHTERM, LASTSEARCH, SEARCHCOUNT) VALUES
>(:SEARCHTERM,cast('now' as timestamp), 1);
>END
>
>The update-thing for an existing record is missing, but even the
>insert-part does not work. Any help is greatly appreciated :)

It's also error-prone if searchterm is not unique in the table - you will get a "Multiple rows in singleton select" error instead of a result. And your SUSPEND is in the wrong place. As it is a scalar result, you need it to be returned when the execution is complete.

And also note your arbitrary use of the colon prefix on variables: you used to need it on variables whenever they were used in SQL statements, otherwise not. In Fb 2, it's optional in SQL statements and is still invalid when you refer to the variable in a PSQL predication or assignment.

Relying on the existence of an ID matching the search term isn't very wise, either. But, as others have said, the reason this is a no-op is that you haven't initialised your variable.

However, the simpler, safer and better performing way to code this is:

ALTER PROCEDURE SP_TBLINDEX_INS_UPD
(
SEARCHTERM VARCHAR(400) CHARACTER SET UTF8 DEFAULT NULL
)
RETURNS
(
SEARCHEXISTS SMALLINT
)
AS
BEGIN
searchexists = 0;
if (exists (
select ID from TBLONE where searchterm = :SEARCHTERM
)
) then
searchexists = 1;

if (SEARCHEXISTS = 0) THEN
insert into TBLONE (SEARCHTERM, LASTSEARCH, SEARCHCOUNT)
VALUES (:SEARCHTERM,cast('now' as timestamp), 1);
SUSPEND;
END

The EXISTS() predicate is very performance friendly: it terminates as soon as one occurrence of the predication is found.

Note: don't ever be tempted to use a selectable SP for the dual purpose of performing DML operations AND returning a result set. It's slightly useful to write an executable SP as a selectable one in the case you have here; but, it's still *better* to make this an executable SP and read the scalar result in the return parameters, if your data access interface supports it...

./heLen