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

    February 6, 2013 Paul Tuohy

    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, as shown in Figure 1.

    Figure 1

    Figure 1: Requested parameter list for the stored procedure sp_move_stock.
    (Click graphic to enlarge.)

    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. Figure 2 shows an example of the output for an overloaded procedure.

    Figure 1

    Figure 2: Requested parameter list for the overloaded stored procedure sp_increase_price. (Click graphic to enlarge.)

    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 sharing Lynne. One more tool in the toolbelt!

    Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page.

    RELATED STORIES

    What’s That Name?

    Index Advisor, Part 2

    Index Advisor, Part 1

    SQL Conference Puts Spotlight on IBM i



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    BCD:  IBM i Webinar - Feb 14th. Web GUI for Green Screens: Modernize Faster with New Presto 4.5
    New Generation Software:  Get the Recipe for easy IBM i Query/Reporting/Analytics - NGS-IQ
    COMMON:  Join us at the 2013 Conference & Expo, April 7 -10 in Austin, TX

    More IT Jungle Resources:

    System i PTF Guide: Weekly PTF Updates
    IBM i Events Calendar: National Conferences, Local Events, and Webinars
    Breaking News: News Hot Off The Press
    TPM @ The Reg: More News From ITJ EIC Timothy Prickett Morgan

    Quantum Launches ‘Pay As You Grow’ Dedupe Boxes Invader II: New Power7+ Machines Take On Entry X86 Iron

    Leave a Reply Cancel reply

Volume 13, Number 3 -- February 6, 2013
THIS ISSUE SPONSORED BY:

ProData Computer Services
Bug Busters Software Engineering
WorksRight Software

Table of Contents

  • What’s That Name, Again?
  • Don’t Clone That Printer File!
  • Admin Alert: When Was The Last Time That Library Got Backed Up And More

Content archive

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

Recent Posts

  • 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
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

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