Creating Result Sets to Return to the Client or Caller | Teradata Vantage - 17.10 - Creating Result Sets to Return to the Client or Caller - 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)

If the CREATE PROCEDURE or REPLACE PROCEDURE statement for the external stored procedure specifies the DYNAMIC RESULT SETS clause, the external stored procedure can create dynamic result sets that are returned to the client application or to the caller of the external stored procedure upon completion of the external stored procedure.

A result set is a set of rows that is the result of a SELECT statement that the external stored procedure executes. An external stored procedure can create up to 15 result sets, depending on the specification of the DYNAMIC RESULT SETS clause.

To create a result set from an external stored procedure:

  1. Follow the basic steps for defining C or C++ external stored procedures that use CLIv2 to directly execute SQL, as described in Overall Procedure.
  2. Submit a single SELECT statement, setting the following options of the DBCAREA.
Option Value Description
Stored Procedure Return Result 2 Return the response to the SELECT statement to the client application.
3 Return the response to the SELECT statement to the caller of the external stored procedure.
4 Return the response to the SELECT statement to the client application and to the external stored procedure.
5 Return the response to the SELECT statement to the caller of the external stored procedure and to the external stored procedure.
Keep Response 'Y' The set of rows returned follows the rules for NO SCROLL cursors.
'P' The set of rows returned follows the rules for SCROLL cursors.

Here is a code excerpt for an external stored procedure that submits a SELECT statement and creates a result set to return to the caller of the external stored procedure:

...

memset(str1, ' ', 100);
sprintf(str1, "SELECT * FROM UserIds WHERE UName = %s;", A_Name);
dbcarea.func             = DBFIRQ;
dbcarea.req_ptr          = str1;
dbcarea.req_len          = strlen(str1);
dbcarea.change_opts      = 'Y';
dbcarea.SP_return_result = 3; 
dbcarea.keep_resp        = 'Y'; 

DBCHCL(&result, cntxt, &dbcarea);

...

After the external stored procedure completes execution, the database returns the result sets created by the external stored procedure to the client or caller.