Determining what SQL statements can be executed in routines

Many, but not all SQL statements can be executed in routines. Execution of a particular SQL statement within a routine is dependent on the type of routine, the implementation of the routine, the maximum SQL access level specified for the routine, and the privileges of the routine definer and invoker.

Before you begin

Determining what SQL statements can be executed within a routine before you implement your routine can ensure that you make the right choice of routine type and implementation from the start.

To successfully execute a SQL statement in a routine, the following prerequisites must be met:
  • The SQL access level of the routine must permit the execution of the particular SQL statement.
    • The SQL access level of a routine is specified in the CREATE statement for the routine.
    • Some SQL access levels are not supported for certain types of routines. Refer to the following restrictions.
  • The routine definer must have the necessary privileges to execute the SQL statement.
    • The privileges required to execute every supported SQL statement are provided in the SQL Reference.
  • No other separate restriction restricts the execution of the statement.
    • Refer to the SQL Reference for a list of restrictions specific to the given SQL statement.


Restrictions

The following restrictions limit the set of SQL statements that can be executed within routines. In particular these restrictions limit what SQL access levels can be specified for particular types of routines:
  • External functions cannot be specified with the MODIFIES SQL DATA access level.
  • External procedures that will be called from a trigger cannot be specified with a MODIFIES SQL DATA access level.

Procedure

To determine what SQL statements can be invoked in a particular routine:

  1. Determine the SQL access level of the routine. If it is an existing routine, examine the CREATE statement that was used to create the routine. The SQL access level clause might be explicitly defined in the DDL as one of: NO SQL, CONTAINS SQL, READS SQL DATA, or MODIFIES SQL DATA. If no such clause is explicitly specified, then the default value for the routine is implied.
    • For SQL procedures the default is MODIFIES SQL DATA.
    • For SQL functions the default is MODIFIES SQL DATA.
    • For external procedures the default is MODIFIES SQL DATA.
    • For external functions the default is READS SQL DATA.
  2. Refer to the table in the topic, "SQL statements that can be executed in routines". Look up the SQL statement of interest by name.
  3. Check if the SQL statement is supported for the specific type of routine and implementation.
  4. Verify that the required SQL access level to execute the statement matches the SQL access level of the routine.
  5. Carefully read any usage notes or footnotes to ensure that there are no other restrictions on the SQL statement execution.

Results

If the SQL statement is indicated as being executable within a routine, the routine SQL access level meets the prerequisites for executing the statement within the routine, and all other prerequisites have been met, the SQL statement should be successfully executable from the routine.