title: Firebird and stored procedures: if exists then else
body: <p>I'm trying to create a stored procedure for firebird 2.1 (this is the version which is to be used)
  But am getting a bit stuck, so any help is appreciated.
  The final version should compare 4 values agains the table, and retreive either the primaryid if the value exists, or create the new entry in the table, and return the new primaryid.</p>
  
  <p>But I get stuck with only one value lookup, and it's not even using the variable yet.</p>
  
  <pre><code>SET TERM ^ ;
  
  CREATE PROCEDURE TESTSP 
   ( A Varchar(64) ) 
  RETURNS 
   ( RESULT Integer )
  AS 
  
  BEGIN
  IF (EXISTS (SELECT PRIMARYID FROM TABLENAME WHERE FIELD = 'Some string')) then
      SELECT PRIMARYID FROM TABLENAME WHERE FIELD = 'Some string' into :primaryid;
      result = PRIMARYID;
  ELSE 
      INSERT INTO TABLENAME (FIELD) VALUES ('Some string');
      result = gen_id(GEN_TABLEID, 0);
  END^
  
  SET TERM ; ^
  </code></pre>
  
  <p>I get a "Token unknown" for the Else command.</p>
  
  <p>Update after responses:
  Now I want to use the 4 variables and return the 4 results.
  I think I need a for loop to do so, but with firebird, the for function means something else.
  So what would be the way to go?</p>
  
  <pre><code>SET TERM ^ ;
  
  CREATE PROCEDURE TESTSP 
   ( value1 Varchar(64) ) 
  RETURNS 
   ( RESULT1 Integer )
  AS 
  
  BEGIN
      IF (EXISTS (SELECT PRIMARYID FROM TABLENAME WHERE FIELD = :value1)) then
          SELECT PRIMARYID FROM TABLENAME WHERE FIELD = value1 into :result1;
      ELSE BEGIN
          result1 = gen_id(GEN_TABLEID, 1);
          INSERT INTO TABLENAME (PRIMARYID, FIELD) VALUES (:result1, :value1);
      END
      suspend;
  END^
  
  SET TERM ; ^
  </code></pre>