Rules for Calling a Procedure With Dynamic Result Sets From Embedded SQL - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™
To be able to access dynamic result sets returned by a procedure called from embedded SQL, you must ensure that two preprocessor options are set properly.
  • Set the SQLCHECK option to FULL so the precompiler can pass the variables that receive the procedure OUT or INOUT parameters to the runtime phase of the preprocessor.

    The following table indicates how to set this option for mainframe-attached and workstation-attached environments:

    Enable SQL Syntax, Object Reference, and Privilege Checking to FULL Option
    Mainframe-attached SQLCHECK(FULL)
    Workstation-attached -sc FULL
  • If you define an embedded SQL cursor for a called procedure, you must also set the preprocessor TRANSACT option to BTET because CALL statements cannot be executed as part of a multistatement request in ANSI session mode.

    The following table indicates how to set this option for mainframe-attached and workstation-attached environments:

    Set the Transaction Mode to BTET Option
    Mainframe-attached TRANSACT(BTET)
    Workstation-attached -tr BTET

See Example: Calling a Stored Procedure that Returns Dynamic Result Sets from Embedded SQL.