Application Development Guide

Valid SQL Procedure Body Statements

A procedure body consists of a single SQL procedure statement. The types of statements that you can use in a procedure body include:

Assignment statement

Assigns a value to an output parameter or to an SQL variable, which is a variable that is defined and used only within a procedure body. You cannot assign values to IN parameters.

CASE statement

Selects an execution path based on the evaluation of one or more conditions. This statement is similar to the CASE expression described in the SQL Reference.

FOR statement

Executes a statement or group of statements for each row of a table.

GET DIAGNOSTICS statement

The GET DIAGNOSTICS statement returns information about the previous SQL statement.

GOTO statement

Transfers program control to a user-defined label within an SQL routine.

IF statement

Selects an execution path based on the evaluation of a condition.

ITERATE statement

Passes the flow of control to a labelled block or loop.

LEAVE statement

Transfers program control out of a loop or block of code.

LOOP statement

Executes a statement or group of statements multiple times.

REPEAT statement

Executes a statement or group of statements until a search condition is true.

RESIGNAL statement

The RESIGNAL statement is used within a condition handler to resignal an error or warning condition. It causes an error or warning to be returned with the specified SQLSTATE, along with optional message text.

RETURN statement

Returns control from the SQL procedure to the caller. You can also return an integer value to the caller.

SIGNAL statement

The SIGNAL statement is used to signal an error or warning condition. It causes an error or warning to be returned with the specified SQLSTATE, along with optional message text.

SQL statement

The SQL procedure body can contain any SQL statement listed in Appendix A, Supported SQL Statements.

WHILE statement

Repeats the execution of a statement or group of statements while a specified condition is true.

Compound statement

Can contain one or more of any of the other types of statements in this list, as well as SQL variable declarations, condition handlers, or cursor declarations.

For a complete list of the SQL statements allowed within an SQL procedure body, see Appendix A, Supported SQL Statements. For detailed descriptions and syntax of each of these statements, refer to the SQL Reference.