16.20 - Overall Procedure - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

Here is a synopsis of the steps you take in an external stored procedure to call a stored procedure:

  1. Define the IN, INOUT, or OUT arguments of the same data type as the stored procedure parameters.

    For example:

    VARCHAR_LATIN regionName[64];
    INTEGER       regionCount;
    
     strcpy((char *)regionName, "Southwest");
  2. Define an array of pointers to the arguments to be passed to or returned from the stored procedure being called. The number of elements in the array must match the number of parameters expected by the stored procedure being called.

    For example:

    void *argv[2];
    
    argv[0] = regionName;
  3. Define an integer array for indicator values that indicate whether arguments passed to or returned from the stored procedure are null. The number of elements in the array must match the number of parameters expected by the stored procedure being called.

    The external stored procedure sets the values corresponding to IN and INOUT arguments. The stored procedure will return values corresponding to INOUT and OUT arguments.

    For example:

    int ind[2];
    
    ind[0] = 0; /* 0 indicates the IN or INOUT value is not null */
  4. Define a parm_t array for the data type, direction (IN, OUT, or INOUT), and attributes of each stored procedure argument. The number of elements in the array must match the number of parameters expected by the stored procedure being called.

    The parm_t structure is defined in sqltypes_td.h as:

    typedef struct parm_t
    {
      dtype_et    datatype;
      dmode_et    direction;
      charset_et  charset;
      union {
        int  length;
        int   intervalrange;
        int   precision;
        struct {
          int  totaldigit;
          int  fracdigit;
        } range;
      } size;
    } parm_t;
    IF an element in the parm_t array corresponds to an argument of this type … THEN the following parm_t members must have values …
    CHAR or VARCHAR
    datatype
    direction
    charset
    size.length
    BYTE or CHARACTER CHARACTER SET GRAPHIC
    datatype
    direction
    size.length
    INTERVAL SECOND(m, n)
    datatype
    direction
    size.range.totaldigit /* m */
    size.range.fracdigit  /* n */
    any other Interval
    datatype
    direction
    size.intervalrange
    TIME(p) or TIMESTAMP(p)
    datatype
    direction
    size.precision /* p */
    DECIMAL(m, n)
    datatype
    direction
    size.range.totaldigit /* m */
    size.range.fracdigit  /* n */
    anything else
    datatype
    direction

    For example:

    parm_t dtype[2];
    
    dtype[0].datatype = VARCHAR_DT;
    dtype[0].direction = IN_PM;
    dtype[0].charset = LATIN_CT;
    dtype[0].size.length = strlen((const char *)regionName);
  5. Call FNC_CallSP.