You are here: SQL Development > SQL Procedures, Functions, and Triggers > SQL Procedural Language > SQL Procedural Conditional Statements

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_expressionsql_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.

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

Examples

One or more of the examples below make use of tables defined in the HOCKEY schema, which is a schema defined in the test database. The test database is created when the QuickStart process is run. See Running the SQL QuickStart.