The following rules and limitations apply to SQL procedure dynamic result sets.
- No parameter can have a TD_ANYTYPE data type.
- You can return dynamic result sets for a single SELECT request only.
Multistatement SELECT requests are not valid for dynamic result sets.
- You cannot return dynamic result sets to both a called procedure and its calling procedure or calling client application.
- You cannot close an open cursor if you want to return dynamic result sets.
- The receiver of a dynamic result set must use its driver (such as CLIv2 or ODBC) to indicate that it will accept the results. See Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems, B035-2417, Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418, and ODBC Driver for Teradata® User Guide for details.
- Use the WITH RETURN ONLY clause to specify that the procedure is to use the cursor as a result set cursor.
- Cursor rules are identical to those for procedures that do not support dynamic result sets (see
Teradata Vantage™ - SQL Stored Procedures and Embedded SQL
, B035-1148
for details) except for the changes and restrictions indicated in the following list.
- You cannot specify PREPARE for a cursor unless it handles result sets.
- You cannot specify OPEN … USING for a cursor unless it handles result sets.
- You cannot specify FOR UPDATE with a WITH RETURN clause.
- If you specify WITH RETURN ONLY …, then the cursor cannot be fetched within the procedure that opened it.
- You cannot close a dynamic result sets cursor once it has been opened because
closing the cursor results in the result set not being returned.
- The returned result set inherits the following CLIv2 response attributes from the caller, not from the procedure that created it.
- Response mode
- Keep response
- LOB response mode
For example, if you submit a CALL from BTEQ that generates a result set, the result set sent to the procedure is in Indicator mode, while the result set sent to BTEQ is in Field mode. See Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems, B035-2417 or Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418 for details.
- The collation sequence for the returned result set is that of the called procedure, not the collation sequence of the caller or the session.
- The starting position of the returned result set and the set of rows in the returned result set is determined by the scrollability of the cursor.
The system always returns the entire result set.
- The various options have performance implications based on how much data the system generates for them as the following table indicates.
Option IF the caller is a procedure THEN the … IF the caller is a client application THEN the … WITH RETURN ONLY - system generates 1 result spool table for calling procedures.
- system generates 1 result spool table for the client application.
The system returns a value of ‘02000’ for SQLSTATE if you attempt to fetch rows from a procedure.
WITH RETURN ONLY TO CLIENT - system generates 1 result spool table for the client application.
- system generates 1 result spool table for the client application.
WITHOUT RETURN - system generates 1 result spool table.
- spool is closed when the procedure exists or exits the compound block cursor in which it is defined.
- system generates 1 result spool table.
- spool is closed when the procedure exists or exits the compound block cursor in which it is defined.
- You can refer to recursive views and recursive queries from a procedure definition.
- You cannot refer to a WITH clause from a procedure definition.