Using Stored Procedure Dynamic Result Sets - Teradata Preprocessor2

Before stored procedure dynamic result sets, it was not possible to call a stored procedure and have the procedure produce a response spool. Instead, it was necessary to create a temporary table for the results, then gather the results by using a SELECT statement after the stored procedure CALL statement. Now, ANSI SQL allows stored procedures to return what are called dynamic result sets. The number of result sets are specified in CREATE PROCEDURE. Each result set returns a result similar to the output of one multi-statement request. The result sets are returned to PP2 using PP2’s client character set and response mode.

When using stored procedure dynamic result sets, precompile the application with SQLCHECK(FULL) and TRANSACT(BTET) options. This is required so that the variables receiving the OUTPUT or INOUT parameters can be passed from the precompiler to the runtime.

When a stored procedure without dynamic result sets is called, SQLCHECK must be specified as FULL:

• -sc FULL for network environments
• SQLCHECK(FULL) if Mainframe

For a stored procedure with dynamic result sets, a cursor is defined, and the transaction mode must be specified as BTET:

• -tr BTET for network environments
• TRANSACT(BTET) for mainframe environments

If BTET is not specified, a 5497 SQLCODE occurs (CALL cannot be submitted in a multi-statement request.)

To declare a cursor to access a stored procedure, use the next example:

EXEC SQL BEGIN DECLARE SECTION;
	...
	long H1;
	long H2;
	long M1;
	long M2;
	long M3;

	...
EXEC SQL END DECLARE SECTION;
	...
EXEC SQL
		DECLARE TESTCUR CURSOR
			FOR 'CALL TESTSP1(:H1, :H2)';
EXEC SQL
		OPEN TESTCUR;

If the cursor is created properly and the stored procedure returns one or more rows, the SQLCODE is set to 3212, indicating that the stored procedure returned a result set.

If the cursor is created properly but the stored procedure returns no rows, the SQLCODE is set to 0.

	if   (SQLCODE == 3212)
		fetch_rows();
	else if (SQLCODE != 0)
		error_check();

Fetching rows from a cursor that is declared for a stored procedure is similar to fetching rows from a cursor defined as an SQL statement.

void fetch_rows()
	do 	{
			EXEC SQL
				FETCH TESTCUR INTO :M1, :M2, :M3;
			...
		} while (SQLCODE == 0);