Previous Topic Next topic Print topic


Declaring Data Types for Passed Parameters

A stored procedure must declare each parameter passed to it. In addition, the DATA TYPE declared in the declaration must be valid for the data source being used and must use a compatible host variable data type when calling the stored procedure. See SQL Data Types for more information.

For example:

CREATE PROCEDURE GETPRML
 (IN  PROCNM CHAR(18)
  ,IN SCHEMA CHAR (8)
  ,INOUT OUTCODE INTEGER
  ,INOUT PARMLIST CHAR (26)
  )
   LANGUAGE COBOL
   DETERMINISTIC
   READS SQL DATA
   NO DBINFO
   FENCED
   NOT THREADSAFE
   EXTERNAL NAME "GETPRML!GETPRML"
   PARAMETER STYLE GENERAL
   PROGRAM TYPE SUB
   RESULT SETS 0;
Note: Specify LANGUAGE COBOL since DB2 LUW does not directly support PL/I as an option. Also, the EXTERNAL name definition should match the case of the PL/I routine.

Define the stored procedure GETPRML as shown to match the definitions in the CREATE PROCEDURE statement:

GETPRML:
  PROC(PROCNM, SCHEMA, OUTCODE, PARMLST)
  external('GETPRML');

  DECLARE PROCNM CHAR(18),               /* INPUT parm -- PROCEDURE name */
          SCHEMA CHAR(8),                /* INPUT parm -- User's SCHEMA  */ 
          OUTCODE FIXED BIN(31) native, /* INOUT -- SQLCODE from        */                                
          PARMLST CHAR(26);              /* INOUT -- CURRENT TIMESTAMP   */
    
  EXEC SQL INCLUDE SQLCA;

  DECLARE TIMESTAMP CHAR(26);

  /************************************************************/
  /* Execute SELECT from SYSIBM.SYSDUMMY1 if TEST passed      */
  /************************************************************/

  if PROCNM = ‘TEST’ then
    do;
      exec sql
        SELECT CURRENT TIMESTAMP INTO :TIMESTAMP
          FROM SYSIBM.SYSDUMMY1;
    end;
  else
    do;
      TIMESTAMP = ‘PASS’;
      SQLCODE = -12;
    end;
      
  PARMLST = TIMESTAMP; 
  OUT_CODE = SQLCODE;         /* return SQLCODE to caller */
  RETURN;
END GETPRML;
Previous Topic Next topic Print topic