AS400 Stored Procedures' Parameters

The System i Navigator will display the information about your AS400's stored procedures but I more often use some version of this SQL statement, modifying the WHERE clause as needed.
select substr(A.SPECIFIC_SCHEMA,1,10) AS "LIB",    
       substr(A.SPECIFIC_NAME,1,15) AS "PROC",     
       B.ORDINAL_POSITION AS "POS",                
       B.PARAMETER_MODE AS "MODE",                 
       substr(B.PARAMETER_NAME,1,15) AS "PARMNAME",
       substr(B.DATA_TYPE,1,15) AS "TYPE",         
       B.CHARACTER_MAXIMUM_LENGTH AS "CHARLEN",    
       B.NUMERIC_PRECISION AS "PRECISION",         
       B.NUMERIC_SCALE AS "SCALE"                  
from qsys2/sysprocs A                              
join qsys2/sysparms B  
on A.SPECIFIC_SCHEMA = B.SPECIFIC_SCHEMA          
and A.SPECIFIC_NAME = B.SPECIFIC_NAME                      
WHERE A.SPECIFIC_SCHEMA = 'GHELTON' 
LIB         PROC                 POS   MODE   PARMNAME         TYPE              CHARLEN    PRECISION      SCALE
GHELTON     SEARCHOTBANDOVL        1   IN     P_PVID           INTEGER                  -          10          0
GHELTON     SEARCHOTBANDOVL        2   IN     P_TAXID          CHARACTER              50             -     
GHELTON     SEARCHOTBANDOVL        3   IN     P_FMTZIP5        CHARACTER               5             -     
GHELTON     SEARCHOTBANDOVL        4   IN     P_FMTZIP9        CHARACTER               9             -     
GHELTON     SEARCHOTBANDOVL        5   IN     P_ORIGIN         CHARACTER              20             -     
GHELTON     SEARCHOTBANDOVL        6   IN     P_TPA            CHARACTER              10             -    
GHELTON     SEARCHOTBANDOVL        7   IN     P_BILENT         CHARACTER              20             -    
GHELTON     SEARCHOTBANDOVL        8   IN     P_TARGETAMOUNT   DECIMAL                  -          20          2
GHELTON     SEARCHOTBANDOVL        9   IN     P_TARGETDATE     CHARACTER              10             -   
GHELTON     SQLRPGJDBC             1   IN     -                CHARACTER              15             -    
GHELTON     SQLRPGJDBC             2   IN     -                CHARACTER              15             -   
GHELTON     UPDATEOUTSIDEVE        1   IN     ID               INTEGER                  -          10          0
GHELTON     UPDATEOUTSIDEVE        2   IN     PHONE            CHARACTER VARYI        20             -    
GHELTON     UPDATEOUTSIDEVE        3   IN     USERID           CHARACTER VARYI        20             -    
GHELTON     UPDATEOUTSIDEVE        1   IN     ID               INTEGER                  -          10          0
GHELTON     UPDATEOUTSIDEVE        2   IN     FAX              CHARACTER VARYI        20             -    
GHELTON     UPDATEOUTSIDEVE        3   IN     PHONE            CHARACTER VARYI        20             -    
GHELTON     UPDATEOUTSIDEVE        4   IN     USERID           CHARACTER VARYI        20             -    
********  End of data  ********  

Blog Archive