Helen, thank you very much for this detailed explanation. Your code
works very well, but I still have a little question:

searchexists = 0;
if (exists (
select ID from TBLONE where searchterm = :SEARCHTERM
)
) then
searchexists = 1;

if (exists) will act like a boolean (or similar), right?. So if there
is a matching record in the table, "searchexists" will be set to "1",
right?
To get the ID of the existing record into the "searchexists":

select ID from TBLONE where searchterm = :SEARCHTERM into searchexists;

But this would fire the select-statement twice, right?
Is there way to do this within a single select?

I need the ID of the existing record to update the
"searchcount"-integer in TBLONE ( with something like "searchcount =
searchcount + 1").

Thanks for your help :)

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
> 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
>