Using Host Variables to Pass Parameter Values

You can specify up to 1023 parameters for passing data between an application and a procedure. You can pass values into a procedure using any SQL expression except a subquery, an aggregate function, or a LONG column, string, TID or Date/Time function. Refer to the ALLBASE/SQL Reference Manual "Expressions" chapter for more information. (Built-in variables cannot be used as parameters, since they are only available within the procedure.) Input parameter values can be passed using host variables or literal values. For any OUTPUT parameters, you must use host variables. (In the EXECUTE PROCEDURE clause of a CREATE RULE statement, you can pass a column name as an input parameter, but column names are not permitted in procedures invoked through the EXECUTE PROCEDURE statement.) Note that this section discusses static parameters. Dynamic parameters are discussed in the following section, "Using Dynamic Procedure Parameters."

The following example shows a procedure definition, followed by an excerpt from an application program that executes the procedure programmatically:

   CREATE PROCEDURE ManufDB.Process12

   (Operator CHAR(20),

   Shift CHAR(20),

   FailureType CHAR(10) NOT NULL) AS

   BEGIN

      INSERT INTO ManufDB.TestMonitor

      VALUES (:Operator, CURRENT_DATETIME,

      :Shift, :FailureType);

      IF ::sqlcode = 0 THEN

         COMMIT WORK;

         RETURN 0;

      ELSE 

         RETURN 1;

      ENDIF;

   END;

The following example shows the execution of procedure ManufDB.Process12 using host variables to pass in the values for Operator, Shift, and FailureType:

First, declare host variables.  Note that the same names are used for host variables in the application program that were used in the 

parameter definitions of the CREATE PROCEDURE statement.  While this is not required, it helps in seeing the relationship between the procedure and the application program.



     C Declarations: COBOL Declarations:



EXEC SQL BEGIN DECLARE SECTION;   EXEC SQL BEGIN DECLARE SECTION END-EXEC.

   char Operator[21];             01   OPERATOR            PIC X(20).

   sqlind OperatorInd;            01   OPERATORIND         SQLIND.

   char Shift[21];                01   SHIFT               PIC X(20).

   sqlind ShiftInd;               01   SHIFTIND            SQLIND.

   char FailureType[11];          01   FAILURETYPE         PIC X(10).

EXEC SQL END DECLARE SECTION;     EXEC SQL END DECLARE SECTION END-EXEC.



A routine within the application program obtains values for

Operator, Shift, and FailureType from a user who enters the data.

If either Shift or Operator is null, the corresponding indicator 

variable is set to -1.



Next, call the procedure to enter the failure information into the database:




   In C:             In COBOL:



EXEC SQL EXECUTE PROCEDURE        EXEC SQL EXECUTE PROCEDURE

ManufDB.Process12(:Operator       ManufDB.Process12 (:OPERATOR

:OperatorInd, :Shift              :OPERATORIND, :SHIFT

:ShiftInd, :FailureType);         :SHIFTIND, :FAILURETYPE) END-EXEC.

Each host variable name in the EXECUTE PROCEDURE statement maps in sequential order to a parameter definition in the CREATE PROCEDURE statement. Inside the procedure, values are referenced by parameter name, not by host variable name. Thus, parameter names and host variable names need not be the same.

As in the example, you use indicator variables in the parameter list of the EXECUTE PROCEDURE statement to indicate null values. But inside the procedure, the parameter itself contains the null value. In order to pass a null operator name to the procedure, you set the indicator variable OperatorInd to -1 and the content of the host variable Operator is undefined. Inside the procedure, the parameter Operator is set to NULL, and this fact can be determined through a test:

   IF :Operator IS NULL THEN

     PRINT 'Parameter is null';

   ELSE 

     PRINT :Operator;

   ENDIF;