602SQL Documentation Index  

_IV_PROCEDURE_PARAMETERS System Query

The system query _iv_procedure_parameters returns a list of procedure parameters stored on the server.

There is one row for each parameter in the query result. There is an extra record for the return value of the function.

Column description

Column name Type Contents
Schema_name CHAR(31) Name of the schema the procedure belongs
Procedure_name CHAR(31) Name of the procedure
Parameter_name CHAR(31) Name of the parameter (empty for the return value of a function)
Ordinal_position INT The ordinal position of the parameter, beginning with 1 (0 for the return value)
Data_type INT The type of the parameter or return value
Length INT Length (only for variable-length types)
Precision INT The number of decimal digits (only for float type numbers)
Direction INT The direction of parameter passing (1 = IN, 2 = OUT, 3 = INOUT, 0 = return value)
Specif INT Specific parameter type (e.g. string sorting method)

Example 1:

This query returns the procedure parameter descriptions from the procedure PROC in the schema APL1.

SELECT * FROM _iv_procedure_parameters 
WHERE schema_name=´APL1´ AND procedure_name=´PROC´

Example 2:

This query returns a list of routines in the opened application, specifies the type of each routine (procedure or function) and the parameter count according to their type (IN, OUT and INOUT). Since the system query _iv_procedure_parameters contains only routines with parameters, the routine list must be selected from the object table.

SELECT Procs.PROC_NAME,COALESCE(SPP.PROC_TYPE,'procedure') AS PROC_TYPE,
  COALESCE(SPP.IN_PARAMS,0) AS `#IN_PARAMS`, COALESCE(SPP.OUT_PARAMS,0) AS `#OUT_PARAMS`,  
  COALESCE(SPP.INOUT_PARAMS,0) AS `#INOUT_PARAMS` 
FROM 
  (SELECT A.obj_name AS PROC_NAME FROM Objtab A, Objtab B 
   WHERE A.apl_uuid=B.apl_uuid AND B.obj_name=CURRENT_APPLICATION AND B.category=Chr(CATEG_APPL) 
     AND A.category=Chr(CATEG_PROC)
  ) AS Procs
  LEFT OUTER JOIN
  (SELECT PROCEDURE_NAME,
     CASE WHEN MIN(direction)=0 THEN 'function' ELSE 'procedure' END AS PROC_TYPE,
     SUM(CASE direction WHEN 1 THEN 1
                        ELSE 0 END) AS IN_PARAMS, 
     SUM(CASE direction WHEN 2 THEN 1
                        ELSE 0 END) AS OUT_PARAMS,
     SUM(CASE direction WHEN 3 THEN 1
                        ELSE 0 END) AS INOUT_PARAMS
  FROM _iv_procedure_parameters WHERE schema_name=CURRENT_APPLICATION
  GROUP BY PROCEDURE_NAME
  ) AS SPP
  ON(Procs.PROC_NAME=SPP.procedure_name)
GROUP BY PROC_NAME