solidDB Help : Programming : SQL extensions : Stored procedures : Executing SQL statements in a stored procedure : Using control statements
  
Using control statements
control_statement ::=
   SET variable_name = value | variable_name ::= value |
      WHILE expression
       LOOP procedure_statement... END LOOP |
       LEAVE |
     IF expression THEN procedure_statement ...
        [ ELSEIF procedure_statement ... THEN] ...
          ELSE procedure_statement ... END IF |
   RETURN | RETURN SQLERROR OF cursor_name | RETURN ROW |
RETURN NOROW
For the full syntax of the CREATE PROCEDURE statement, see CREATE PROCEDURE.
You can use the following control statements in stored procedures:
 
Control statement
Description
SET variable = expression
Assigns a value to a variable. The value can be either a literal value (for example, 10 or 'text') or another variable. Parameters are considered as normal variables.
variable ::= expression
Alternate syntax for assigning values to variables.
WHILE
   
expr
LOOP
   statement-list
END LOOP
Loops while expression is true.
LEAVE
Leaves the innermost while loop and continues executing the procedure from the next statement after the keyword end loop.
IF
   
expr
THEN
   
statement-list1
ELSE
   statement-list2
END IF
Executes statements-list1 if expression expr is true; otherwise, executes statement-list2.
IF
   
expr1
THEN
   
statement-list1
ELSEIF
   expr2
THEN
   statement-list2
END IF
If expr1 is true, executes statement-list1. If expr2 is true, executes statement-list2. The statement can optionally contain multiple elseif statements and also an else statement.
RETURN
Returns the current values of output parameters and exits the procedure. If a procedure has a return row statement, return behaves like return norow.
RETURN SQLERROR OF cursor-name
Returns the sqlerror associated with the cursor and exits the procedure.
RETURN ROW
Returns the current values of output parameters and continues execution of the procedure. Return row does not exit the procedure and return control to the caller.
RETURN NOROW
Returns the end of the set and exits the procedure.
Note A procedure that is defined with the RETURNS clause returns always at least one row. This happens even if the row is NULL.
To prevent the return of empty rows, use RETURN NOROW in the procedure body. It returns immediately without a result set (SQL_NO_DATA). The statement RETURN NOROW can be used after a sequence of RETURN ROW statements whereby the procedure returns the rows generated with RETURN ROW.
Go up to
Executing SQL statements in a stored procedure