Indicator variables and stored procedures

variables and stored procedures

Indicator variables can be used with the CALL statement, provided host variables are used for the parameters, to pass additional information to and from the procedure. Indicator variables are the SQL standard means of denoting that the associated host variable should be interpreted as containing the null value, and this is their primary use.

To indicate that an associated host variable contains the null value, the indicator variable, which is a two-byte integer, is set to a negative value. A CALL statement with indicator variables is processed as follows:

* If the indicator variable is negative, this denotes the null value. A default value is passed for the associated host variable on the CALL and the indicator variable is passed unchanged.
* If the indicator variable is not negative, this denotes that the host variable contains a non-null value. In this case, the host variable and the indicator variable are passed unchanged.

These rules of processing are the same for input parameters to the procedure as well as output parameters returned from the procedure. When indicator variables are used with stored procedures, the correct method of coding their handling is to check the value of the indicator variable first before using the associated host variable.

The following example illustrates the handling of indicator variables in CALL statements. Notice that the logic checks the value of the indicator variable before using the associated variable. Also note the method that the indicator variables are passed into procedure PROC1 (as a third argument consisting of an array of two-byte values).

Assume a procedure was defined as follows:

CREATE PROCEDURE PROC1
(INOUT DECIMALOUT DECIMAL(7,2), INOUT DECOUT2 DECIMAL(7,2))
EXTERNAL NAME LIB1.PROC1 LANGUAGE RPGLE
GENERAL WITH NULLS)


Figure 5. Handling of Indicator Variables in CALL Statements

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Program CRPG
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
D INOUT1 S 7P 2
D INOUT1IND S 4B 0
D INOUT2 S 7P 2
D INOUT2IND S 4B 0
C EVAL INOUT1 = 1
C EVAL INOUT1IND = 0
C EVAL INOUT2 = 1
C EVAL INOUT2IND = -2
C/EXEC SQL CALL PROC1 (:INOUT1 :INOUT1IND , :INOUT2
C+ :INOUT2IND)
C/END-EXEC
C EVAL INOUT1 = 1
C EVAL INOUT1IND = 0
C EVAL INOUT2 = 1
C EVAL INOUT2IND = -2
C/EXEC SQL CALL PROC1 (:INOUT1 :INOUT1IND , :INOUT2
C+ :INOUT2IND)
C/END-EXEC
C INOUT1IND IFLT 0
C* :
C* HANDLE NULL INDICATOR
C* :
C ELSE
C* :
C* INOUT1 CONTAINS VALID DATA
C* :
C ENDIF
C* :
C* HANDLE ALL OTHER PARAMETERS
C* IN A SIMILAR FASHION
C* :
C RETURN
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
End of PROGRAM CRPG
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Program PROC1
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
D INOUTP S 7P 2
D INOUTP2 S 7P 2
D NULLARRAY S 4B 0 DIM(2)
C *ENTRY PLIST
C PARM INOUTP
C PARM INOUTP2
C PARM NULLARRAY
C NULLARRAY(1) IFLT 0
C* :
C* INOUTP DOES NOT CONTAIN MEANINGFUL DATA
C*
C ELSE
C* :
C* INOUTP CONTAINS MEANINGFUL DATA
C* :
C ENDIF
C* PROCESS ALL REMAINING VARIABLES
C*
C* BEFORE RETURNING, SET OUTPUT VALUE FOR FIRST
C* PARAMETER AND SET THE INDICATOR TO A NON-NEGATIV
C* VALUE SO THAT THE DATA IS RETURNED TO THE CALLING
C* PROGRAM
C*
C EVAL INOUTP2 = 20.5
C EVAL NULLARRAY(2) = 0
C*
C* INDICATE THAT THE SECOND PARAMETER IS TO CONTAIN
C* THE NULL VALUE UPON RETURN. THERE IS NO POINT
C* IN SETTING THE VALUE IN INOUTP SINCE IT WON'T BE
C* PASSED BACK TO THE CALLER.
C EVAL NULLARRAY(1) = -5
C RETURN
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
End of PROGRAM PROC1
+++++++++++++++++++++++++++++++++++++++++++++++++++