|
VADELIA |
SADELIA |
WADELIA |
EADELIA |
(I/B) (S) |
(B) (S) |
(I/B) (S) |
(E) (S) |
Warning:
This instruction is not compatible with AS/400 server parts.
Section for use
All
Syntax
EXEC_PROC_SQL 'ProcedureName' ParameterList ReturnParameter
ReturnParameter |
→ |
*RETURN(HostVarId) | Null |
ParameterList |
→ |
ParameterSeries | Null |
ParameterSeries |
→ |
ParameterSeries, Parameter | Parameter |
Parameter |
→ |
Constant | HostVarId Kind |
HostVarId |
→ |
:VarId | :VarId :IndicatorVarId |
Kind |
→ |
I | O | B | Null |
Constant |
→ |
AlphaConstant | NumConstant |
Description
This instruction calls a SQL stored procedure returning its results via the defined output parameters or input/output parameters, and/or via a result parameter.
Only procedures taking simple types as a parameter and returning a single result are supported. Procedures returning a set of results (cursor) are not supported.
Parameters can be of any simple type generated by Adelia apart from IMAGE type.
The *RETURN parameter is used to define the variable receiving the procedure's return value, when this returns a result.
Note that the *RETURN parameter is not necessarily supported by all the database managers implementing stored procedure support. In particular, it is not supported by DB2.
The parameter kind is specified with one of the following three letters:
I: input parameter.
O: output parameter.
B: input/output parameter.
If the kind is not specified, the parameter is considered to be an input parameter (I).
After executing the instruction, the SQL return code can be tested using the reserved word *SQLCODE.
NB:
Using the ',' character as both a parameter separator and decimal separator may lead to ambiguity.
In this case, the character is evaluated as a decimal separator as a priority.
So 21,22,23 in the parameter specification is interpreted as the definition of two parameters: the first being 21.22 and the second 23.
To express two integer parameters, you must put a space before or after the comma: 21, 22 (rather than 21,22).
For example
* Create the GetServerName procedure which sends the database server name
SWITCH *DBMS
IF_VALUE 'ORACLE'
Query = 'create or replace procedure GetServerName (vServerName out varchar2) as begin select distinct rtrim(machine) into vServerName from sys.v_$session where username is null; end;'
exec_sql(:Query)
...
END_SWITCH
* Call the procedure
EXEC_PROC_SQL 'GetServerName' :ServerName O