• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru Classic: What’s That Name, Again?

    March 6, 2019 Paul Tuohy

    Author’s Note: This article was originally published in February 2013. I recently had to hunt out this article when visiting a client. The content is even more relevant as the use of SQL stored procedures continues to grow.

    One of the great things about writing articles and tips is that people will drop you a note to ask a question or tell you how much they liked (or disliked) what you wrote. But even better is when someone drops you a line to let you know they have taken what you wrote and extended it.

    Such was the case with my last tip, What’s That Name?, which described how to go about getting a “record layout” in Run SQL Scripts. I received an email from Lynne Noll, who said she had just written 128 stored procedures, and had even more trouble remembering parameters and their order than she had remembering field names in a table. I can believe it!

    So, with Lynne’s kind permission, here is the stored procedure she wrote (with only a couple of minor tweaks), which provides the list of parameters for a stored procedure.

    The Stored Procedure

    The code that follows shows the CREATE PROCEDURE statement for the procedure GETPARMS. The salient points are:

    • The procedure accepts two parameters: The name of parameter, and the name of the schema. NOTE:*LIBL or blank or acceptable for the schema name. The UPPER function is used with the parameters so we do not have to worry about case sensitivity.
    • A cursor is declared for a SELECT statement that accesses the relevant information from the views QSYS2/SYSPROCS and QSYS2/SYSPARMS.
    • The procedure returns a result set that lists the position, name, and definition of each parameter.
    • We will talk about the last column (PROCEDURE_SPECIFIC_NAME) in just a moment.
    CREATE PROCEDURE GETPARMS (
       IN PROC_NAME CHAR(128),
       IN SCHEMA_NAME CHAR(128)
       ) 
       DYNAMIC RESULT SETS 1
       LANGUAGE SQL
       SPECIFIC GETPARMS
       NOT DETERMINISTIC
       READS SQL DATA 
       BEGIN 
        DECLARE C1 CURSOR FOR SELECT
    	 B.ORDINAL_POSITION AS ORD,
    	  TRIM(SUBSTR(B.PARAMETER_NAME,1,20)) 
           AS PARAMETER_NAME,
    	  TRIM(SUBSTR(B.DATA_TYPE,1,15)) AS TYPE,
    	 DEC(COALESCE(B.CHARACTER_MAXIMUM_LENGTH,
             B.NUMERIC_PRECISION,0),5,0) AS SIZE,
    	 DEC(IFNULL( B.NUMERIC_SCALE,0),5,0) AS DECS,
    	   TRIM(A.SPECIFIC_NAME) || ' IN ' || 
           TRIM(A.ROUTINE_SCHEMA) AS PROCEDURE_SPECIFIC_NAME
    	    
            FROM QSYS2/SYSPROCS  A INNER JOIN QSYS2/SYSPARMS B
    		  ON A.SPECIFIC_NAME=B.SPECIFIC_NAME AND
    		  A.SPECIFIC_SCHEMA=B.SPECIFIC_SCHEMA
    		   
             WHERE (A.ROUTINE_SCHEMA=UPPER(SCHEMA_NAME) OR
    		   '*LIBL'=UPPER(SCHEMA_NAME) OR
    		   ' '=SCHEMA_NAME)  AND
    		   A.ROUTINE_NAME=UPPER(PROC_NAME)
    		    
             ORDER BY A.ROUTINE_SCHEMA, A.SPECIFIC_SCHEMA,
                   A.SPECIFIC_NAME, ORD
    
             FOR READ ONLY ; 
          OPEN C1 ;
          SET RESULT SETS CURSOR C1 ;
       END  ;
    

    The procedure returns the list of parameters for the requested procedure (in this case the stored procedure sp_move_stock).

    Nice and easy to use. But why that PROCEDURE_SPECIFIC_NAME column at the end?

    Overloading

    One of the really neat things about stored procedures is that they can be overloaded. This means that you can have multiple procedures with the same name but different parameter lists. Overloaded procedures are uniquely identified with a SPECIFIC NAME. That is what is used in the join logic between QSYS2/SYSPROCS and QSYS2/SYSPARMS.

    Since our stored procedure accepts the procedure name, and not the specific name, as a parameter: we need to ensure that we return details of all stored procedures with that name and identify which is which. Hence the PROCEDURE_SPECIFIC_NAME column at the end. This is an example of the output for the overloaded procedure sp_increase_price.

    Ready To Go

    Now, as long as you can remember the name of GETPARMS and what parameters are required – you are ready to go.

    Thank you for sharing, Lynne. One more tool in the tool belt!

    Paul Tuohy, IBM Champion and author of Re-engineering RPG Legacy Applications, is a prominent consultant and trainer for application modernization and development technologies on the IBM Midrange. He is currently CEO of ComCon, a consultancy firm in Dublin, Ireland, and partner at System i Developer. He hosts the RPG & DB2 Summit twice per year with partners Susan Gantner and Jon Paris.

    RELATED STORIES

    What’s That Name?

    What’s That Name, Again?

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guruclassic, FHGC, Four Hundred Guru Classic, IBM i, SQL

    Sponsored by
    OCEAN User Group

    OCEAN TechCon25 Online

    It’s an Exciting Time for IBM i !

    July 16 & 17, 2025 – ONLINE

    Two virtual days of learning, presented by an outstanding group of IBM’ers and IBM Champions, featuring leading-edge topics.

    FREE for OCEAN members!

    Register NOW!

    Annual (12-month) Individual OCEAN Memberships are $80 and a Corporate Membership is $250. A Corporate Membership would allow your entire company to have full access to the OCEAN website & video library and to attend OCEAN events at member rates. Act now because rates are increasing on August 1, 2025.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Guru Classic: Custom Perspectives In RDi, Part 2 Enterprises Spend On Systems, Hyperscalers Tap The Brakes

    Leave a Reply Cancel reply

TFH Volume: 29 Issue: 14

This Issue Sponsored By

  • RPG & DB2 Summit
  • RPG & DB2 Summit
  • RPG & DB2 Summit

Table of Contents

  • Guru Classic: What’s That Name, Again?
  • Guru Classic: Custom Perspectives In RDi, Part 2
  • Guru Classic: A Bevy of BIFs, %XLATE and %REPLACE

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle