Stored Procedures

This topic describes how to manage stored procedures in CA RC/Update.
stored procedure
is a routine that can be called to perform operations. A stored procedure can be either of the following types:
  • 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. 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, specify the Language and Fenced options to determine the options that are made available to complete the definition. The following fields appear on the create, alter, or template panel:
  • Language
    Determines the language that the procedure is written in.
  • Fenced
    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 these settings. As a result, you only have to be concerned with attributes that are applicable to the type of procedure with which you are working. When you change the setting of Language or Fenced, the 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. Use this list to manage parameter values, including the following information:
  • 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. If no distinct types or parameters are defined for the procedure, the command has no effect on the procedure.