Wednesday, December 23, 2009

Refined Stored Procedure Parameter Display

select substr(A.SPECIFIC_SCHEMA,1,10) AS "LIB",                     
       substr(A.SPECIFIC_NAME,1,12) AS "PROC",                      
              ORDINAL_POSITION AS "POS",                            
              PARAMETER_MODE AS "MODE",                             
       substr(PARAMETER_NAME,1,11) AS "PARMNAME",                   
       substr(DATA_TYPE,1,12) AS "TYPE",                            
       case when DATA_TYPE IN ('REAL', 'BIGINT', 'DOUBLE PRECISION',
            'DECIMAL', 'INTEGER', 'NUMERIC', 'SMALLINT', 'NUMERIC') 
            then trim(CHAR(NUMERIC_PRECISION)) CONCAT ', ' CONCAT   
                 trim(CHAR(NUMERIC_SCALE))                          
            else CHARLEN CONCAT '  ' END CASE                       
from qsys2/sysprocs A join qsys2/sysparms B on A.SPECIFIC_SCHEMA =  
B.SPECIFIC_SCHEMA and A.SPECIFIC_NAME = B.SPECIFIC_NAME             
where A.SPECNAME = 'SOMEPROC'