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:
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: Requested parameter list for 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. Figure 2 shows an example of the output for an overloaded procedure.
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 SQL Conference Puts Spotlight on IBM i
|
