Hi,

>well my gui does´nt support the set term statement (EMS SQL Manager),
>but nevertheless I got it almost working:
>
>CREATE PROCEDURE SP_TBLONE_INS_UPD(
> SEARCHTERM VARCHAR(400) CHARACTER SET UTF8 DEFAULT NULL)
>RETURNS(
> SEARCHEXISTS SMALLINT)
>AS
>DECLARE VARIABLE getid INTEGER;
>DECLARE VARIABLE idcount INTEGER;
>BEGIN
>getid = NULL;
>idcount = null;
>
>SELECT ID, SEARCHCOUNT
>FROM TBLONE
>WHERE SEARCHTERM = :SEARCHTERM
>INTO :getid, :idcount;
>
>IF ( getid IS NULL ) THEN
>BEGIN
>INSERT INTO TBLONE
>( SEARCHTERM, LASTSEARCH, SEARCHCOUNT)
>VALUES
>(:SEARCHTERM,cast('now' as timestamp), 1 );
>end
>
>if (getid IS NOT NULL) then
>begin
>idcount = idcount + 1;
>update TBLONE
>SET SEARCHCOUNT = :idcount
>where ID = :getid;
>END
>
>SUSPEND;
>END;
>
>Now the following happens:
>I the getid is null both conditions seem to be true, because the
>insert AND the update path will be followed... :(

That's cannot be, unless you're modifying GETID in the first
BEGIN .. END block that does the insert.

Either way, why not use ELSE instead of IF (getid IS NOT NULL)

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com