17.10 - Syntax - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
Language
English (United States)
void *
FNC_CallSP( SQL_TEXT *SP_Name,
            int       argc,
            void     *argv[],
            int       ind[],
            parm_t    dtype[],
            char     *sqlstate )

Syntax Elements

SP_Name
Name of the stored procedure to invoke:
  • The name can be qualified by the name of the database in which the stored procedure resides.
  • The name can be up to 128 characters.
argc
Count of arguments to pass to the stored procedure. The value must match the number of parameters expected by the stored procedure being called.
argv
Entry points to the argument values to be passed or returned from the stored procedure being called. The number of elements in argv must match the number of parameters expected by the stored procedure being called.
ind
Indicators corresponding to the arguments of the stored procedure being called, in the same order.
If the argv element is an IN or INOUT argument, and the value is:
  • null, then set the corresponding element in ind to -1.
  • not null, then set the corresponding element in ind to 0.
For elements of argv that are OUT or INOUT arguments of the stored procedure being called, the value of the corresponding element in ind will be returned by the stored procedure being called, according to the preceding rules.
dtype
Data type, attributes, and direction (IN, OUT, or INOUT) of each argument being passed to or returned from the stored procedure.
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 {
    long  length;
    int   intervalrange;
    int   precision;
    struct {
      int  totaldigit;
      int  fracdigit;
    } range;
  } size;
} parm_t;
where:
  • datatype specifies the data type of the argument. The sqltypes_td.h header file defines dtype_et as:
    typedef int dtype_et;

    Valid values are defined by the dtype_en enumeration in sqltypes_td.h:

    typedef enum dtype_en {
        UNDEF_DT=0,
        CHAR_DT=1,
        VARCHAR_DT=2,
        CLOB_REFERENCE_DT=34,
        BYTE_DT=3,
        VARBYTE_DT=4,
        BLOB_REFERENCE_DT=33,
        GRAPHIC_DT=5,
        VARGRAPHIC_DT=6,
        BYTEINT_DT=7,
        SMALLINT_DT=8,
        INTEGER_DT=9,
        BIGINT_DT=36,
        REAL_DT=10,
        DECIMAL1_DT=11,
        DECIMAL2_DT=12,
        DECIMAL4_DT=13,
        DECIMAL8_DT=14,
        DECIMAL16_DT=37,
        NUMBER_DT=38,
        DATE_DT=15,
        TIME_DT=16,
        TIMESTAMP_DT=17,
        INTERVAL_YEAR_DT=18,
        INTERVAL_YTM_DT=19,
        INTERVAL_MONTH_DT=20,
        INTERVAL_DAY_DT=21,
        INTERVAL_DTH_DT=22,
        INTERVAL_DTM_DT=23,
        INTERVAL_DTS_DT=24,
        INTERVAL_HOUR_DT=25,
        INTERVAL_HTM_DT=26,
        INTERVAL_HTS_DT=27,
        INTERVAL_MINUTE_DT=28,
        INTERVAL_MTS_DT=29,
        INTERVAL_SECOND_DT=30,
        TIME_WTZ_DT=31,
        TIMESTAMP_WTZ_DT=32,
        UDT_DT=35,
        FNC_DATATYPESETSIZE=39
    } dtype_en;
  • direction specifies the direction of the argument (IN, OUT, or INOUT).

    The sqltypes_td.h header file defines dmode_et as:

    typedef int dmode_et;

    Valid values are defined by the dmode_en enumeration in sqltypes_td.h:

    typedef enum dmode_en
       {
          UNDEF_PM=0,
          IN_PM=1,
          INOUT_PM=2,
          OUT_PM=3
       } dmode_en;
  • charset specifies the character set of CHAR or VARCHAR data.

    The sqltypes_td.h header file defines charset_et as:

    typedef int charset_et;

    Valid values are defined by the charset_en enumeration in sqltypes_td.h:

    typedef enum charset_en
       {
          UNDEF_CT=0,
          LATIN_CT=1,
          UNICODE_CT=2,
          KANJISJIS_CT=3,
          KANJI1_CT=4
       } charset_en;
  • size.length specifies the length of a CHAR, VARCHAR, BYTE, or CHARACTER CHARACTER SET GRAPHIC type.
  • size.intervalrange specifies the range of an INTERVAL type. For example, 4 for INTERVAL YEAR(4).
  • size.precision specifies the precision in a TIME or TIMESTAMP type. For example, 4 for TIME(4).
  • size.range.totaldigit specifies the value m in a DECIMAL(m,n) or INTERVAL SECOND (m,n) type.
  • size.range.fracdigit specifies the value n in a DECIMAL(m,n) or INTERVAL SECOND (m,n) type.
sqlstate
a pointer to a char[6] array that is used to return the result of executing the called stored procedure.