1  CASE_Searched
   Executes one of a sequence of alternate statement blocks in
   a compound statement of a multistatement procedure. Unlike
   the simple CASE control statement, the searched CASE control
   statement supports arbitrary predicates for the WHEN clause that
   can contain variable and parameter references.
 

2  Environment
   You can use the searched CASE control statement in a compound
   statement of a multistatement procedure:

   o  In interactive SQL

   o  Embedded in host language programs to be precompiled

   o  As part of a procedure in an SQL module

   o  In dynamic SQL as a statement to be dynamically executed
 

2  Format

 case-searched-statement =
 
 CASE -----+--> WHEN -> predicate -> THEN -----+
           |  +--------------------------------+
           |  +-+-> compound-use-statement -+-++
           |    +------------ <-------------+ ||
           +----------------- <---------------+|
   +-------------------------------------------+
   +-+--------------------------------------+--> END CASE -->
     +-> ELSE -+-> compound-use-statement -++
               +------------- <------------+

 

2  Arguments
 

3  ELSE

   Specifies the set of SQL statements to be executed when the WHEN
   clause evaluates to FALSE or UNKNOWN.
 

3  THEN

   Specifies the set of SQL statements to be executed when the WHEN
   clause evaluates to TRUE.
 

3  WHEN

   Determines whether the compound use statements in the THEN clause
   are to be executed or the compound use statements in the ELSE
   clause are to be executed. If the predicate evaluates to TRUE,
   then the compound use statements in the THEN clause are executed.
   If the predicate evaluates to FALSE or UNKNOWN, then the compound
   use statements in the ELSE clause are executed.
 

2  Examples
   Example 1: Specifying Predicates with Variable References

   SQL> CREATE TABLE T (C INT);
   SQL> BEGIN
   cont> DECLARE :V INTEGER = 10;
   cont> DECLARE :X INTEGER = 0;
   cont> CASE
   cont>   WHEN :V = 1 THEN INSERT INTO T(C) VALUES (:X + 1);
   cont>   WHEN :V = 2 THEN INSERT INTO T(C) VALUES (:X + 2);
   cont>   WHEN :V > 3 THEN INSERT INTO T(C) VALUES (:X);
   cont>   ELSE INSERT INTO T(C) VALUES (-1);
   cont> END CASE;
   cont> END;