Stored procedures provide an easy way to reuse existing RPG logic or to make new RPG code available to other platforms or environments.

Stored procedures are programs that are called from SQL. These programs can be written using the SQL procedure language, but they may also be written using RPG. Since stored procedures are  called from SQL, creating one is often a simple way to make application functions available from non-RPG application environments, such as a Java application or an application running on a different system platform. 

If you know SQL procedure language and think the application function is well-suited for SQL, then perhaps you would write the stored procedure using SQL. On the other hand, you may choose to write the stored procedure in RPG, perhaps because you and your fellow developers are more familiar with writing and maintaining RPG logic or because RPG is better-suited for the specific task (e.g., maybe you need to use some functions that are easier to do in RPG than SQL, such as %EDITC or %EDITW). An even better reason to use RPG is that you may very well already have a program or procedure written in RPG that can now be reused from another environment.

In this article, I will focus on some specific details of writing  RPG external stored procedures, with particular focus on some areas that are not well-documented in other sources.

So how do you turn an RPG program into a stored procedure? It can be very simple; you need to enter the "Create Procedure" SQL statement, which specifies the name of the program to be called and the parameter list it requires. The RPG program itself does not need to use embedded SQL, although it may do so. When a stored procedure is written in a language other than SQL, such as RPG, it is referred to as an "external stored procedure." The program name is supplied using the "External Name" parameter, and the parameters are described immediately after the stored procedure name. The statement below creates a simple stored procedure from an RPG program named PRODINFO.

CREATE PROCEDURE   GetProdInfo     

  (IN ProdID CHAR (5), OUT Name CHAR (20),

   OUT Category CHAR(15),  OUT Price DEC(5,2))

   EXTERNAL NAME  MyLib/ProdInfo               

   LANGUAGE  RPGLE                       

   PARAMETER STYLE GENERAL

Note that the stored procedure name may or may not be the same as the program name. The parameters are listed as either input or output (INOUT is the option for both input and output). Of course, RPG technically treats all parameters to programs as both input and output, but logically, most parameters are typically either providing input to the program from the caller or sending output back to the caller. The Create Procedure statement must be entered using an SQL interface, such as interactive SQL or Navigator.

It's a good idea to specify the language of the program (or procedure) that is being designated as a stored procedure, although in most cases, if the program already exists, the system will find it and use the language of the program object.

You may be interested to know that procedures in service programs may also be used as stored procedures but only if they do not have a return value-in other words, if they are called using CALLP rather than called from an expression. The syntax for the External Name parameter in that case would look like this: 

EXTERNAL NAME MyLib/MySrvPgm(ProdInfo) 

The Parameter Style keyword is an important one and is the primary focus of this article. In this example, we used "General," which is the simplest form of passing parameters. With the General style, the parameters listed on the Create Procedure statement can be matched exactly one to one with the entry parameters in the program. While this may seem obvious, we'll look at some other parameter styles in which the relationship between the two parameter lists is not so obvious.

Parameter Style Differences

Based on the parameter list and the "General" parameter style specified in the statement above, the PRODINFO program's procedure interface (PI) might look like the following. For those of you who don't use PIs for programs, this is the equivalent to the *Entry PList. While technically your RPG program is not required to use a PI to be a stored procedure, most modern RPGers are switching to replacing their *Entry PLists with PIs for all new RPG programs, so I'll use them in my examples here.

     D ProdInfo        PI

     D  ProdID                        5A

     D  ProdName                     20A

     D  ProdCat                      15A

     D  ProdPrc                       5P 2

Once you have written the PRODINFO RPG program (if it doesn't already exist) and entered the Create Procedure SQL statement for it, it is ready to be used as a stored procedure. It can be that simple! The procedure can be called from an SQL statement that might look something like the following. I've used RPG syntax with embedded SQL here since most of you are likely familiar with that interface. The syntax for the call from another platform and/or language may be different.

    Call GetProdInfo( :ProdNo, :ProdName, :Category, :Price)

There are many other Create Procedure parameters and options that can be used, but since most of them are rarely used, I'll leave them for you to study on your own using the documentation. However, the impact of the parameter style on the requirements for your RPG program may not be obvious from the documentation, so I will take a closer look at that here.

Depending on the environment calling the stored procedure, it may be necessary to use a parameter style other than the "General" style used here. The RPG program code must change to match the parameter style needed. For example, many SQL environments require the use of null indicators, so your stored procedure would need to be able to support those as part of the parameter list. Parameter style General does not support null indicators, but the other styles do, so let's take a look at how to code the parameter list to handle some other commonly used styles.

The simplest of the parameter styles that support null indicators is called "General with Nulls" (which is sometimes called "Simple, allow null values"). In this case, the parameters as listed on the Create Procedure statement do not change. However, the parameter list (or procedure interface, PI) in the program must change. To create a stored procedure using this parameter style, the Create Procedure statement would look like the following:

CREATE PROCEDURE   GetProdInfo     

  (IN ProdID CHAR (5), OUT Name CHAR (20),

   OUT Category CHAR(15),  OUT Price DEC(5,2))

   EXTERNAL NAME  MyLib/ProdInfo               

   LANGUAGE  RPGLE                       

   PARAMETER STYLE GENERAL WITH NULLS

However, the program's PI would look like this:

     D ProdInfo        PI

     D  ProdID                        5A

     D  ProdName                     20A

     D  ProdCat                      15A

     D  ProdPrc                       5P 2

     D  NullInds                             5I 0 Dim(4)

Note that even though the Create Procedure statement still specifies four parameters, this program now receives five parameters. The additional parameter is an array that contains the same number of elements as there are parameters, not counting itself (four in our example). Note that even though they are referred to as "null indicators," they are not indicator data types. They are 2-byte integer values, which are best coded in RPG IV as a 5-digit integer data type (5i 0). It is important to notice that this fifth parameter is not included in the list of parameters at the beginning of the Create Procedure statement. It is an implicit parameter generated because of the parameter style.

Each element of the NullInds array will contain a value to indicate whether its corresponding parameter should be considered null. In this example, the first element on NullInds corresponds to ProdID, the second element corresponds to ProdName, etc. If the null indicator value for a parameter (i.e., the array element corresponding to a parameter) has a value of -1, the related parameter is null and any value that may appear in that field should be ignored. If the value of the field is not null, the null indicator has a value of 0. Note that for parameters that are either Input or InOut, the program should check the corresponding null indicator value passed before using the value in each parameter. Likewise, if the program wants to indicate that one of the Out or InOut values passed back to the caller is null, it should place a value of -1 in the appropriate null indicator. In this example, you may decide to write the stored procedure program to return nulls for all the output fields if no product was found that matched the product ID supplied in the input parameter. In that case, you should set NullInds(2), NullInds(3), and NullInds(4) to a value of -1 before returning to the caller.

If you prefer, you could also define that fifth implicit parameter as a data structure with four subfields. In this case, the following D specs may be used:

     D ProdInfo        PI

     D  ProdID                        5A

     D  ProdName                     20A

     D  ProdCat                      15A

     D  ProdPrc                       5P 2

     D  NullInds                                  LikeDS(NullDS)

     D NullDS           DS

     D  ProdID_NI                     5I 0

     D  ProdName_NI                   5I 0

     D  ProdCat_NI                    5I 0

     D  ProdPrc_NI                    5I 0

With this alternative code in place, you now have specific names for each null indicator value, so to make the output parameters null, you would place a value of -1 in fields named NullInds.ProdName_NI, NullInds.ProdCat_NI, and NullInds.ProdPrc_NI. Keep in mind that the use of the LikeDS keyword means the null indicator field names are implicitly qualified. 

Calling this version of the stored procedure with the null indicators might look like the following code.

  Call GetProdInfo( :ProdNo, :ProdName :Name_NI, :Category :Cat_NI, :Price :Price_NI)

Note that null indicator fields immediately follow the parameter field with no comma in between. If the calling program knows a field should never be null, the call statement need not include a null indicator for that field. In this example, the caller knows it will never pass in a null product ID value, so it can leave out the indicator for that field. If it seems odd to you that the call statement should list the fields and indicators in this sequence when the RPG program accepts them in a completely different sequence and format, join the club! I can't explain why the parameters are reformatted, but remember that the call to your program is actually coming via the database (that's where all SQL statements are processed). So the program call to your RPG program is generated on the System i, and that's how it's possible for the transformation of format and sequence of parameters to happen.

The parameter lists get even stranger when using other parameter styles. There is a parameter style known as SQL, where not only are null indicators passed as individual parameters, but some other information is also passed. The Create Procedure statement for creating an SQL parameter style stored procedure would look like this:

CREATE PROCEDURE   GetProdInfo     

  (IN ProdID CHAR (5), OUT Name CHAR (20),

   OUT Category CHAR(15),  OUT Price DEC(5,2))

   EXTERNAL NAME  MyLib/ProdInfo               

   LANGUAGE  RPGLE                       

   PARAMETER STYLE SQL

Note that the same four fields are specified as parameters as was true in the first two versions of this procedure. However, the PI for this version of the program would look like the following:

     D ProdInfo        PI

     D  ProdID                        5A

     D  ProdName                     20A

     D  ProdCat                      15A

     D  ProdPrc                       5P 2

     D  ProdID_NI                     5I 0 

     D  ProdName_NI                   5I 0 

     D  ProdCat_NI                    5I 0 

     D  ProdPrc_NI                    5I 0 

     D  RtrnSQLStt                    5A

     D  ProcName                    517A   Varying

     D  SpecName                    128A   Varying

     D  MsgTxt                       70A   Varying

This example has two significant differences from the "General with Nulls" one. Most notably, you can see that some extra pieces of information are implicitly passed at the end of the list. Look closely and you'll notice something else unusual. As with the "General with Nulls" style, null indicator variables are defined in the parameter list for each "real" parameter passed, and they are each defined as a 5-digit integer as before. But this time, each null indicator is passed individually, whereas last time, all the null indicators were passed in a single structure or array. This is a significant detail that is not always obvious from the documentation or the examples of RPG stored procedures provided.

The additional parameters passed at the end of the SQL style list include some extra information and also some support for error or special condition handling. The field I've named RtrnSQLStt is an output parameter that you can use to return a valid "SQL State" value to the caller. For those of you unfamiliar with SQL State, it's a return code field that can be used with embedded SQL to reflect the status of the last SQL statement. It serves a similar function to resulting indicators or built-in functions such as %EOF, %Found, and %Error. Those of you who use embedded SQL are probably familiar with the SQL Code return code. SQL State provides the same function as SQL Code and is the more modern way of dealing with conditions since the values are standard across all SQL platforms.

A value of all zeroes in the SQL State field (named RtrnSQLStt in our example) indicates that the stored procedure was run with no errors or special conditions to report back to the caller. It will be filled with zeroes when your program is called. There is a special range of values that you can use to report errors to your caller, so if you plan to use the SQL parameter style, make sure you study the possible values to use. There is also a companion message text field at the end of the parameter list if you need to include more information about the condition.

The first of the other two extra fields simply contains the name of the procedure that was called (GetProdInfo in our example). The second is known as the "specific name." Remember that with an external stored procedure, it would be possible to use the same program object to create stored procedures with different names. If it is important to the program logic to know which procedure name was used to call it, this will provide that information.

The bottom line is that stored procedures provide a very easy way to reuse existing RPG logic or to make new RPG code available to other platforms or environments. As you can see, the primary challenge that exists is simply figuring out what changes, if any, will be needed in the RPG program's parameter list to make it work as a stored procedure. I have found that the details about these parameter list differences are hard to come by-at least in terms that make sense to RPGers. Most of the examples in IBM documentation and Redbooks are not written in RPG. Even non-IBM examples that I found in Internet searches tend to focus on one parameter style, so finding details about the differences can be difficult. My goal was to share the results of my studies on this topic, and I hope it will be of help to those of you struggling to find information about how to make RPG stored procedures work.