RE: SQL Stored Procedures

[Alan Campin <Alan.Campin@xxxxxxx>]

Here is my parameter list from my AS400DATE using SQL with NULL's. In this 
case, the list is for a procedure for a service program but same list for a 
regular program. This is one of 8 procedures. 

     ,*-------------------------------------------------------------------------
     ,* AS400DATE_PACKED80
     ,*  This function receives a numeric field hopefully containing a
     ,*    date. The second parameter provides the format of the numeric
     ,*    field. The functions is defined using DB2SQL parameters.
     ,*    Function just maps to integer and calls AS400DATE_INT.
     ,* Parms
     ,*   InDate80          - Date to convert.
     ,*   InDateFormat      - Format.
     ,*   OutDate           - Date to return to SQL Statement.
     ,*   InNumericDateNull - Null Indicator.
     ,*   InDateFormatNull  - Null Indicator.
     ,*   OutDateNull       - Null Indicator.
     ,*   InOutSQLState     - SQL State.
     ,*   InSQLFunctionName - SQL Function being called.
     ,*   InSQLSpecificName - SQL Specific Function being called.
     ,*   OutMessageText    - SQL Message Text to Return.
     ,*  Returns - N/A
     ,*-------------------------------------------------------------------------
     p AS400DATE_PACKED80...
     p                 b                   Export
     d                 pi
     d  InDate80...
     d                                8p 0
     d  InDateFormat...
     d                                8a   Varying
     d  OutDate...
     d                               10a
     d  InDate80Null...
     d                                     Like(StdNullInd)
     d  InDateFormatNull...
     d                                     Like(StdNullInd)
     d  OutDateNull...
     d                                     Like(StdNullInd)
     d  OutSQLState...
     d                                     Like(StdSQLState)
     d  InFunctionName...
     d                                     Like(StdSQLFuncName)
     d  InSpecificName...
     d                                     Like(StdSQLSpecName)
     d  OutMessageText...
     d                                     Like(StdSQLMsgText)

Here is SQL function declaration:

 Create Function AS400DATE (Dec(8,0), VarChar(8))       
   Returns Date Cast From Char(10)                      
   Language RPGLE                                       
   External Name 'QGPL/XVAS4D(AS400DATE_PACKED80)'    
   Deterministic                                        
   No SQL                                               
   Parameter Style SQL                                  
   Allow Parallel                                       
   No External Action;  
                  
This is part of documentation from AS400DATE. Hope this makes everything clear 
as mud.

Using parameter style SQL results in a parameter list coming into a function 
of: 
  
d AS400DATE_PACKED80...
d                 pr
d  PR_InDate80...
d                                8p 0
d  PR_InDateFormat...
d                                8a   Varying
d  PR_OutDate...
d                               10a
d  PR_InNumericDateNull...
d                                     Like(StdNullInd)
d  PR_InDateFormatNull...
d                                     Like(StdNullInd) 
d  PR_OutDateNull...
d                                     Like(StdNullInd)
d  PR_OutSQLState...
d                                     Like(StdSQLState)
d  PR_InFunctionName...
d                                     Like(StdSQLFuncName)
d  PR_InSpecificName...                                       
d                                     Like(StdSQLSpecName)
d  PR_OutMessageText...
d                                     Like(StdSQLMsgText)     
  
The first three fields are the incoming date in 8,0 and the date format. The 
next three are for null indicators. These allow you to return a null result to 
SQL. 
  
The SQLState allows you to return an error to the calling SQL function. If the 
data is invalid, you have two choices. Return a null value or end the function 
with an error. 
  
FunctionName and SpecificName just pass the name of the functions calling and 
MessageText allows you to return up to 70 characters of message text to be 
displayed when you return an error. 
  
Using a parameter style like "General" would result in nothing being received 
but the parameters you specified and you could not error out or return a null. 
Normally, you would want to use parameter style SQL.   
              
-----Original Message-----
From: Jon Paris [mailto:Jon.Paris@xxxxxxxxxxxxxx]
Sent: Sunday, May 29, 2005 4:31 PM
To: Rpg400
Subject: SQL Stored Procedures


I have looked at so many contradictory examples of Stored procedures that my
eyes have gone foggy!

Can somebody with more experience than me please confirm the following:

If you specify PARAMETER STYLE GENERAL then you get passed one parameter for
each of the parameters declared to the procedure.

If you add WITH NULLS then the first group of parms is followed by an equal
number of parms representing the null flags.

Because the null flags are consecutive in memory, defining an array as the
first null flag parm allows you to access null flags 1 to n.

If you specify STYLE SQL then to the above list you add additional parms for
SQLSTATE and a bunch of other stuff. However, in this case - even when using
an array over the null indicators - you must still specify the other n -1
null parameters (even if you don't use them) or the SQLSTATE etc. parms will
not line up.

So - if I have 2 parms and use STYLE SQL, then my parm list looks like this:

Parm1
Parm2
Null_Ind_Parm1  (Can specify Dim(2) here)
Null_Ind_Parm2
SQLSTATE
etc.


Jon Paris
Partner400

www.Partner400.com
www.RPGWorld.com