7. OpenSQL Features : Database Procedures : Register Procedure Statement—Register Database Procedure
 
Share this page                  
Register Procedure Statement—Register Database Procedure
The REGISTER PROCEDURE statement defines the interface between an application and a database procedure when support for the CREATE PROCEDURE statement is not available. Creation and maintenance of the database procedure is dependent on the host DBMS, Enterprise Access, or EDBC.
The REGISTER PROCEDURE statement defines the procedure name, its parameters and their types, and the host DBMS, Enterprise Access, or EDBC information required to access the procedure.
Non-row returning REGISTER PROCEDURE syntax:
REGISTER PROCEDURE procedure_name
        [(parameter_definition {,parameter_definition})]
        AS IMPORT FROM '...'
        WITH [with_clause]
Note:  The BYREF keyword can be used for parameters.
Row-returning REGISTER PROCEDURE syntax:
REGISTER PROCEDURE procedure_name
        [(parameter_definition {,parameter_definition})]
        AS IMPORT FROM '...'
        RESULT ROW (return_type_list);
Note:  The BYREF keyword cannot be used for parameters.
procedure_name
Specifies the procedure name to be used in the OpenSQL EXECUTE PROCEDURE or REMOVE PROCEDURE statements.
as import from
Specifies host DBMS, Enterprise Access, or EDBC information required to identify the procedure being registered.
with with_clause
Specifies additional information that may be required by an Enterprise Access or EDBC product.
return_type_list
Lists data types.
The parameter_definition is specified as:
parameter_name datatype [NOT | WITH NULL]
[NOTE DEFAULT | [WITH] DEFAULT [default_value]]
[BYREF]
[IS host_info]
Parameters are nullable unless you specify NOT NULL.
The default_value can be a numeric or character literal or one of the following constants: NULL, USER, CURRENT_DATE, or CURRENT_TIME. If the default value is omitted, a system‑generated default is assigned. If the DEFAULT clause is omitted, DEFAULT NULL is assumed.
The BYREF keyword specifies that the parameter is passed by reference, enabling the procedure to return a value in the parameter. The BYREF keyword must also be used in the EXECUTE PROCEDURE statement to obtain the returned value. BYREF cannot be used if the procedure returns rows.
The IS clause specifies additional information about the parameter, as required by the host DBMS, Enterprise Access, or EDBC product. The host information must be enclosed in single quotes.