CODEWITHSUNDEEP
stored-proceduresif-statementfirebird
Giancarlo
Giancarlo

Reputation: 399

Firebird and stored procedures: if exists then else

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.

But I get stuck with only one value lookup, and it's not even using the variable yet.

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 ; ^

I get a "Token unknown" for the Else command.

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?

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 ; ^

Upvotes: 2

Views: 22458

Answers (3)

tico
tico

Reputation: 124

If you have multiple instruction for then and/ or else clause you must use BEGIN ... END-block!

SET TERM ^ ;

CREATE PROCEDURE TESTSP 
 ( A Varchar(64) ) 
RETURNS 
 ( RESULT Integer )
AS 

BEGIN
IF (EXISTS (SELECT PRIMARYID FROM TABLENAME WHERE FIELD = 'Some string')) then
  BEGIN
    SELECT PRIMARYID FROM TABLENAME WHERE FIELD = 'Some string' into :primaryid;
    result = PRIMARYID;
  END
ELSE 
  BEGIN
    INSERT INTO TABLENAME (FIELD) VALUES ('Some string');
    result = gen_id(GEN_TABLEID, 0);
  END
END^

SET TERM ; ^

Upvotes: 1

guetlaur
guetlaur

Reputation: 11

I think your stored procedure should look like this:

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 :result;
  ELSE BEGIN
     result = gen_id(GEN_TABLEID, 1); 
     INSERT INTO TABLENAME 
       (PRIMARYID, FIELD) 
       VALUES (:result,  'Some string'); 
  END
END^ 
SET TERM ; ^

Upvotes: 1

ain
ain

Reputation: 22749

As Tico already answered you have to use begin / end to group multiple statements in then / else part. The error abut column PRIMARYID being unknown is because you reference to it without having declared a local variable for it. Try this:

CREATE PROCEDURE TESTSP ( A Varchar(64) ) 
RETURNS ( RESULT Integer )
AS 
BEGIN
  -- initialize the result
  Result = NULL;
  -- check is the string already in table
  SELECT PRIMARYID FROM TABLENAME WHERE FIELD = 'Some string' into :Result;
  IF (Result is NULL) then
    INSERT INTO TABLENAME(PRIMARYID, FIELD) VALUES(gen_id(GEN_TABLEID, 1), 'Some string') RETURNING PRIMARYID INTO :Result;
END^

Upvotes: 1

Related Questions