Using Conditions to Control the Flow of Execution

IF and CASE conditional statements let you execute alternative commands based on certain conditions.

IF

NuoDB has four forms of IF:

IF (boolean-expression)
    statements (1)
END_IF;
IF (boolean-expression)
    statements (1)
[ELSE
    statements] (2)
END_IF;
IF (boolean-expression)
    statements (1)
[ELSE_IF (boolean-expression)
    statements] (1)
END_IF;
IF (boolean-expression)
    statements (1)
[ELSE_IF (boolean-expression)
    statements] (1)
[ELSE
    statements] (2)
END_IF;
1 These statements are executed if the preceding boolean-expression returns TRUE.
2 These statements are executed if the preceding boolean-expression returns FALSE.
Multiple ELSE_IF blocks can be nested under an IF statement.

CASE

CASE provides conditional execution based on equality of operands. The search-expression is evaluated (once) and successively compared to each expression in the WHEN clauses. If a match is found, then the corresponding statements are executed, and then control passes to the next statement after END. (Subsequent WHEN expressions are not evaluated). If no match is found, the ELSE statements are executed. If no ELSE statement is provided, CASE returns NULL.

CASE search-expression
        WHEN expression THEN
              statements
        [ ELSE
              statements ]
 END;

Examples

Example 1: Using the IF conditional statement.
DROP PROCEDURE IF EXISTS prc_get_player;
SET DELIMITER @
CREATE PROCEDURE prc_get_player (IN iType string, IN iPlayer string)
 AS
    var outstr string;
    IF (iType = 'Stats')
        outstr=(SELECT 'Hgt/Wgt='|| Height ||'/'||Weight
                  FROM PLAYERS
                 WHERE PLAYERID = iPlayer);
    ELSE
        outstr=(SELECT 'Years Played from '||firstnhl||' to '||lastnhl
                  FROM PLAYERS
                 WHERE PLAYERID = iPlayer);
    END_IF;
    THROW outstr;
END_PROCEDURE;
@
SET DELIMITER ;

CALL prc_get_player('Stats','harrija01');
Procedure HOCKEY.PRC_GET_PLAYER, Hgt/Wgt=76/211

CALL prc_get_player('Years','harrija01');
Procedure HOCKEY.PRC_GET_PLAYER, Years Played from 2005 to 2011
Example 2: Using the CASE conditional statement.
DROP PROCEDURE IF EXISTS prc_get_player;
SET DELIMITER @
CREATE PROCEDURE prc_get_player (IN iType string, IN iPlayer string)
 AS
    var outstr string;
    outstr=
    CASE iType
        WHEN 'Stats' THEN (SELECT 'Hgt/Wgt='|| Height ||'/'||Weight
                  FROM PLAYERS
                 WHERE PLAYERID = iPlayer)
        ELSE (SELECT 'Years Played from '||firstnhl||' to '||lastnhl
                  FROM PLAYERS
                 WHERE PLAYERID = iPlayer)
    END;
    THROW outstr;
END_PROCEDURE;
@
SET DELIMITER ;

CALL prc_get_player('Stats','harrija01');
Procedure HOCKEY.PRC_GET_PLAYER, Hgt/Wgt=76/211

CALL prc_get_player('Years','harrija01');
Procedure HOCKEY.PRC_GET_PLAYER, Years Played from 2005 to 2011