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 two forms of IF:

IF (boolean-expression) statements END_IF;
 
IF (boolean-expression) statements ELSE statements END_IF;

IF Example:

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

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;

CASE Example:

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