Stored Procedures

stored procedure
is a routine that can be called to perform operations. A stored procedure can be either of the following:
  • SQL procedure
    Contains only SQL statements and can be a
    SQL procedure or
    SQL procedure.
  • External procedure
    Contains host language statements (and might contain SQL statements).
With stored procedures, common code can be called from several programs. Host languages can call procedures that exist on the local system. Additionally, SQL can call a procedure on a remote system, which means you can use SQL procedures to enhance performance of distributed applications.
Language and Fenced Options
When you define a stored procedure, the following fields on the create, alter, or template panel determine what options are made available for completing the definition:
  • Language (which determines the language in which the procedure will be written)
  • Fenced (which determines whether the procedure runs in an external address space)
The header portion of the panel is dynamically built and allows modification only to attributes that are applicable, based on the settings of Language and Fenced. As a result, you do not have to be concerned with attributes that are not applicable to the type of procedure with which you are working. When you change the setting of Language or Fenced, these other available attributes change accordingly. For complete information about Language, Fenced, and all other fields, see the online help.
Parameter Declaration
The Parameter Declaration List is a scrollable region on the procedure create, alter, and template panels. You can use this list to manage parameter information, including the following:
  • Number of parameters for your stored procedure
  • Data type of each parameter
  • Name of each parameter
  • Inclusion of temporary transition tables
For distinct types specified by the DATA TYPE and SCHEMA fields in all parameter declarations, you can automatically convert the types to the built-in type upon which each distinct type is sourced.
To convert distinct types to their respective source types, enter 
in the command line of any procedure create, alter, or template panel.
This command is valid
when the Parameter Declaration List is displayed. Additionally, if no distinct types or parameters are defined for the procedure, the command has no effect on the procedure.