SQL Procedural Conditional Statements

NuoDB SQL supports the following conditional SQL statements for use in SQL stored procedures, user defined functions (UDFs), or trigger.

Syntax

IF ( sql_boolean_expression | sql_query_statement )
    sql_code_block
    [ ELSE sql_code_block ]
END_IF;

WHILE ( sql_boolean_expression | sql_query_statement )
    sql_code_block
END_WHILE;

TRY sql_code_block
    CATCH(error_variable)
    sql_code_block
END_TRY;

[ [ VAR ] var_name var_type [ , var_name var_type ]... = ]
    CASE input_expression
        WHEN (when_expression) THEN result_expression
        [ WHEN (when_expression) THEN result_expression ]...
        [ ELSE result_expression ]
    END;

Description

An IF statement evaluates the results from the sql_boolean_expression or the boolean result returned from the sql_query_statement and if true, transfers control to the next sql_code_block or if false, transfers control to the sql_code_block following the ELSE keyword.

A WHILE statement evaluates the results from the sql_boolean_expression or the boolean result returned from the sql_query_statement and executes the following sql_code_block until the time at which the boolean WHILE condition evaluates to false.

The TRY statement executes the following sql_code_block and if an error is thrown, the name of the error condition is assigned to error_variable for reporting purposes, and execution continues with the sql_code_block following the CATCH keyword.

The CASE statement takes as an input_expression a valid SQL expression that can be compared to the following series of WHEN expressions. If the input_expression is equal to one of the specified WHEN expressions (when_expression) then the result_expression following its THEN keyword will be executed. If the input_expression is not equal to any of the specified WHEN expressions, then if an ELSE expression is specified, its result_expression is executed.

The CASE statement will return the value of the evaluated result_expression and this can be assigned to one or more variables. See Example 4.

Parameters

sql_boolean_expression: A valid SQL expression that results in true or false.
sql_query_statement: A string representing a valid SQL query statement that results in a boolean true or false value. Any SQL query that returns a non-empty result set, evaluates as true. See SELECT for syntax of a valid SELECT statement. .
error_variable: A variable to which the error incurred in a TRY/CATCH block is assigned.
input_expression: In a CASE statement, a valid SQL expression that can be compared to when_expression and may or may not be assigned one of the specified result_expression s.
when_expression: In a CASE statement, a valid SQL expression to be compared with input_expression.
result_expression: In a CASE statement, a valid SQL expression that can be assigned to input_expression, when the corresponding when_expression evaluates equally.

Examples

Example 1: Using a WHILE loop within a stored procedure

The FOR SELECT statement needs to provide an alias, using AS, for the playerid column. This is necessary because the variable playerid, which is returned from the FOR SELECT statement, is being used in the SELECT statement of the INSERT statement inside the FOR SELECT loop. Without this alias name, the SELECT statement inside the INSERT statement would treat playerid as a column in one of the tables being selected from. And in this case, since both the scoring table and the players table contains a column named playerid, an ambiguous column error would result.

SET DELIMITER @
CREATE PROCEDURE prc_player_teams (in_lastname STRING) /* IN is implied */
    RETURNS team_tab (player_name STRING
                     ,year        INTEGER
                     ,stint       INTEGER
                     ,team_name   STRING
                     ,conf_div    STRING
                     ,rank        INTEGER
                     ,record      STRING)
AS
    FOR SELECT p1.playerid AS P1_PLAYER_ID ,p1.firstnhl ,p1.lastnhl
          FROM hockey.players p1
          WHERE UPPER(p1.LASTNAME) LIKE UPPER(in_lastname)||'%';
       VAR v_nhlyear INTEGER = firstnhl;
       WHILE (v_nhlyear < lastnhl)
           INSERT INTO team_tab SELECT p.firstname||' '||p.lastname
                                      ,s.year
                                      ,s.stint
                                      ,t.name
                                      ,conferenceid||' '||divisionid
                                      ,t.rank
                                      ,wins||'-'||losses||'-'||ties
                                 FROM hockey.players p
                                   JOIN hockey.scoring s ON s.playerid = p.playerid
                                   JOIN hockey.teams t   ON t.teamid = s.teamid
                                WHERE p.playerid = P1_PLAYER_ID
                                  AND p.playerid = s.playerid
                                  AND s.teamid = t.teamid
                                  AND s.year = v_nhlyear
                                  AND t.year = v_nhlyear
                                ORDER BY p.lastname,s.year,s.stint;
           v_nhlyear=v_nhlyear +1;
       END_while;
   END_FOR;
END_PROCEDURE
@
SET DELIMITER ;

CALL prc_player_teams('Arnason');
  PLAYER_NAME  YEAR  STINT        TEAM_NAME       CONF_DIV  RANK   RECORD
 ------------- ----- ------ --------------------- --------- ----- --------
 Chuck Arnason 1971    1    Montreal Canadiens      0 ED      3   46-16-16
 Chuck Arnason 1972    1    Montreal Canadiens      0 ED      1   52-10-16
 Chuck Arnason 1973    1    Atlanta Flames          0 WD      4   30-34-14
 Chuck Arnason 1973    2    Pittsburgh Penguins     0 WD      5   28-41-9
 Chuck Arnason 1974    1    Pittsburgh Penguins     WA NO     3   37-28-15
 Chuck Arnason 1975    1    Pittsburgh Penguins     WA NO     3   35-33-12
 Chuck Arnason 1975    2    Kansas City Scouts      CC SM     5   12-56-12
 Chuck Arnason 1976    1    Colorado Rockies        CC SM     5   20-46-14
 Chuck Arnason 1977    1    Colorado Rockies        CC SM     2   19-40-21
 Chuck Arnason 1977    2    Cleveland Barons        WA AD     4   22-45-13
 Chuck Arnason 1978    1    Minnesota North Stars   WA AD     4   28-40-12
 Chuck Arnason 1978    2    Washington Capitals     WA NO     4   24-41-15
 Tyler Arnason 2001    1    Chicago Blackhawks      WC CE     3   41-27-13
 Tyler Arnason 2002    1    Chicago Blackhawks      WC CE     3   30-33-13
 Tyler Arnason 2003    1    Chicago Blackhawks      WC CE     5   20-43-11
 Tyler Arnason 2005    1    Chicago Blackhawks      WC CE     4   26-43-0
 Tyler Arnason 2005    2    Ottawa Senators         EC NE     1   52-21-0
 Tyler Arnason 2006    1    Colorado Avalanche      WC NW     4   44-31-0
 Tyler Arnason 2007    1    Colorado Avalanche      WC NW     2   44-31-0
 Tyler Arnason 2008    1    Colorado Avalanche      WC NW     5   32-45-0

Example 2: Using TRY/CATCH to execute a test statement and handle errors

In the following examples, for the last execution of the stored procedure, the TRY statement will successfully insert the row into the table, which means the CATCH statement does not execute. However, since statements executed from inside a stored procedure are executed as a single transaction, by default, the THROW statement will cause the inserted row to be rolled back.

DROP PROCEDURE IF EXISTS prc_hockey_create;
SET DELIMITER @
CREATE PROCEDURE prc_hockey_create (inNum STRING
  , inName STRING
  , inPos STRING
  , inTeam STRING)
 RETURNS trace (log STRING,num STRING,name STRING)
AS
  TRY
        INSERT INTO HOCKEY (number,name,position,team)
          VALUES (inNum, inName, inPos, inTeam);
 CATCH(error)
        INSERT INTO trace VALUES (error, inNum,inName);
      RETURN ;
 END_TRY;
 THROW 'record valid for insert';
END_PROCEDURE
@
SET DELIMITER ;

CALL prc_hockey_create('abc','Tom Jones','Goalie','Bruins');
                        LOG                         NUM    NAME
 -------------------------------------------------- ---- ---------
 unable to convert string "abc" into type "integer" abc  Tom Jones
 ``
CALL prc_hockey_create('40','TUUKKA RASK','Goalie','Bruins');``
                                     LOG                                     NUM     NAME
 --------------------------------------------------------------------------- ---- -----------
 duplicate value in unique index PLAYER_IDX, key = '40, TUUKKA RASK, Bruins'  40  TUUKKA RASK

CALL prc_hockey_create('99','Tom Jones','Goalie','Bruins');
Procedure HOCKEY.PRC_HOCKEY_CREATE, record valid for insert

Example 3: Using TRY/CATCH to control error handling and continue processing

In this example we have data loaded into a staging table. We want to validate the data and only load rows that are valid into the permanent table.
If a row is not valid, we want to log it to the error logging table with the error.

DROP PROCEDURE IF EXISTS prc_insert_validate;
DROP TABLE IF EXISTS error_log;
DROP TABLE IF EXISTS perm_tbl;
DROP TABLE IF EXISTS stage_tbl;
CREATE TABLE error_log (error_msg string
                       ,record string);

CREATE TABLE perm_tbl (id integer not null primary key generated always as identity
                       , name string
                       , City string
                       , state string CHECK (state = 'MA'));
CREATE UNIQUE INDEX idx_unique_perm_tbl_name ON perm_tbl(name);

INSERT INTO perm_tbl (name, city, state) VALUES
  ('Mary Doe','Boston', 'MA');

CREATE TABLE stage_tbl ( name string
                       , City string
                       , state string);

INSERT INTO stage_tbl (name, city, state) VALUES
  ('Tom Jones','Any City', 'USA'),('Bob Smith','Cambridge', 'MA'),
  ('Mary Doe','Boston', 'MA'),('Thomas Brady','Foxboro', 'MA');

SET DELIMITER @
CREATE PROCEDURE prc_insert_validate ()
AS
    VAR v_name string;
    VAR v_city string;
    VAR v_state string;
    FOR SELECT name, city, state FROM stage_tbl;
        v_name = name;
        v_city = city;
        v_state = state;
        TRY
            INSERT INTO perm_tbl (name, city, state)
                VALUES (v_name, v_city, v_state);
        CATCH(error)
            INSERT INTO error_log
                VALUES (error, 'record: '||v_name||' '||v_city||' '||v_state);
        END_TRY;
    END_FOR;
END_PROCEDURE
@
SET DELIMITER ;

CALL prc_insert_validate;

SELECT * FROM perm_tbl;
 ID      NAME       CITY    STATE
 --- ------------ --------- ------
  1  Mary Doe     Boston      MA
  3  Bob Smith    Cambridge   MA
  5  Thomas Brady Foxboro     MA

SELECT * FROM error_log;
                                 ERROR_MSG                                              RECORD
 -------------------------------------------------------------------------- ------------------------------
 violation of constraint "STATE"                                            record: Tom Jones Any City USA
 duplicate value in unique index IDX_UNIQUE_PERM_TBL_NAME, key = 'Mary Doe' record: Mary Doe Boston MA

Example 4: Using CASE statement within a stored procedure

SET DELIMITER @
CREATE PROCEDURE get_stats(IN  in_stat   STRING,
                           IN  in_year   INTEGER,
                           IN  in_teamid STRING,
                           OUT out_stat1 STRING,
                           OUT out_stat2 STRING,
                           OUT out_stat3 STRING)
AS
    out_stat1,out_stat2,out_stat3 =
        CASE in_stat
            WHEN 'Conference'
                THEN (SELECT conferenceid, divisionid, rank
                         FROM hockey.teams
                         WHERE year = in_year
                         AND teamid = in_teamid)
            WHEN 'Record'
                THEN (SELECT wins, losses, ties
                         FROM hockey.teams
                         WHERE year = in_year
                         AND teamid = in_teamid)
        END;
END_PROCEDURE
@

CREATE PROCEDURE get_team_stats(IN in_stat   STRING,
                                IN in_year   INTEGER,
                                IN in_teamid STRING)
AS
    VAR out1 STRING, out2 STRING, out3 STRING;
    EXECUTE get_stats(in_stat,in_year,in_teamid,out1,out2,out3);
    VAR display_out STRING;
    display_out=
        CASE in_stat
            WHEN 'Conference'
                THEN 'Conference='||out1||' Division='||out2||' Rank='||out3
            WHEN 'Record'
                THEN 'Wins='||out1||' Loses='||out2||' Ties='||out3
        END;
    THROW display_out;
END_PROCEDURE
@
SET DELIMITER ;

EXECUTE get_team_stats('Conference',2011,'BOS');
Procedure HOCKEY.GET_TEAM_STATS, Conference=EC Division=NE Rank=1
EXECUTE get_team_stats('Record',2011,'BOS');
Procedure HOCKEY.GET_TEAM_STATS, Wins=49 Loses=29 Ties=0