602SQL Documentation Index  
<­RETURN Statement UpControl Statements CASE Statement­>

IF Statement (SQL)

statement_IF ::= IF condition THEN statement …
[ ELSEIF condition THEN statement … ] …
[ ELSE statement … ]
END IF;

IF is a statement that makes a decision based on a comparison. The conditions are evaluated one after another until the first that equals TRUE is found. The statements following this condition are then executed. If no condition is fulfilled (all equal FALSE or UNKNOWN), the statements following ELSE are executed.

Example 1:

A trigger that ensures the entered number is the absolute value.

TRIGGER befupd BEFORE UPDATE OF NUMINT ON Tab2
REFERENCING NEW ROW AS newrow
FOR EACH ROW
BEGIN
  IF newrow.intnumber < 0 THEN
    SET newrow.intnumber=-newrow.intnumber;
  END IF;
END

Example 2:

A function that checks the validity of a login to an application:

FUNCTION AUTH_GET_USERNAME(IN username CHAR(254),IN password CHAR(254)) RETURNS CHAR(35);
/*****************************************************/
// returns the name of the user set by the login name and password 
// returns NULL if the user does not exist
BEGIN
  DECLARE name CHAR(35);
  DECLARE EXIT HANDLER FOR SQLSTATE '21000' BEGIN 
    CALL Log_write('Handler in Auth_get_username - user duplicity');
    RETURN NULL;
  END;

  IF EXISTS(SELECT id FROM Business_partners 
            WHERE intr_user=username AND intr_pswd=password )
  THEN 
    SELECT title1 INTO name FROM  Business_partners 
    WHERE intr_user=username AND intr_pswd=password;
    RETURN name;
  ELSE RETURN NULL;
  END IF;
END
<­RETURN Statement UpControl Statements CASE Statement­>