Using CLIv2 to Consume Dynamic Result Sets | Stored Procedure Examples | Vantage - Using CLIv2 to Consume Dynamic Result Sets - Analytics Database - Teradata Vantage

SQL External Routine Programming

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2023-07-11
dita:mapPath
iiv1628111441820.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1147
lifecycle
latest
Product Category
Teradata Vantage™

This example uses CLIv2 in an external stored procedure to execute an SQL request to call a stored procedure that returns dynamic result sets.

The external stored procedure does the following:
  • Drops and creates a table called TraceTable that it uses to trace the types of parcels that are returned to the external stored procedure as a result of calling the stored procedure.
  • Drops and creates a table called tab1.
  • Inserts 10 rows into tab1.
  • Calls the p1 stored procedure, which selects the 10 rows from the tab1 table. For each parcel returned, the external stored procedure inserts that into the TraceTable table.

The stored procedure that returns the result sets is defined like this:

REPLACE PROCEDURE P1 (IN OUT_SQLSTR VARCHAR(50))
DYNAMIC RESULT SETS 1
BEGIN
   DECLARE C1 CURSOR WITH RETURN ONLY TO CALLER FOR S1;
   PREPARE S1 FROM OUT_SQLSTR;
   OPEN C1;
END;

For details on the REPLACE PROCEDURE statement and the DYNAMIC RESULT SETS clause, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144. For details on the DECLARE CURSOR, PREPARE, and OPEN statements, see Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148.