Teradata Database has the capability for a stored procedure to return one or more result sets in addition to the output parameters being returned.
A stored procedure returns a result set to the client by creating a database cursor and not closing it before returning. A cursor opened by a stored procedure is positioned where the stored procedure left it off. A result set returned from a stored procedure is presented by ODBC as an ODBC cursor just like any other result set.
Note: Using the OutputAsResultSet option is not recommended, because it is deprecated. For details, see “DSN Option to Return Output Parameters as ResultSet” on page 165.
The OutputAsResultSet ODBC data source configuration option determines how stored procedure output parameters are to be delivered to the application. If set to Yes, the values of INOUT and OUT parameters are returned as a single-row result set. If set to No (default), these parameters are returned as bound ODBC parameters. If OutputAsResultSet is set to Yes and the application also expects dynamic result sets from SPs, then the single-row result set with parameter values is the first result set returned and the application should use SQLMoreResults to position to the subsequent result sets. This is standard ODBC practice when dealing with multiple result sets.
The following example describes the usage of the stored procedure CALL statement in an ODBC application when the OutputAsResultSet option is set to N:
Assume a stored procedure spODBC has three parameters: p1 of type OUT, p2 of type INOUT, and p3 of type IN.
The“?” character acts as a placeholder for IN, OUT, and INOUT arguments. The “?” character placeholder arguments need to be bound with the application local variables using the SQLBindParameter ODBC SDK API call.
{
char *request = "CALL spODBC(?, ?, ?)";
...
SQLBindParameter(..., 1, SQL_PARAM_OUTPUT, ..., SQLINTEGER,
..., ..., AppVar1, sizeof(AppVar1), ...);
SQLBindParameter(..., 2, SQL_PARAM_INPUT_OUTPUT, ..., SQLINTEGER,
..., ..., AppVar2, sizeof(AppVar2), ...);
SQLBindParameter(..., 3, SQL_PARAM_INPUT,..,SQLINTEGER, ...,...,AppVar3, sizeof(AppVar3),...);
...
SQLExecDirect(hstmt, request);
...
}
where:
AppVar1, AppVar2, and AppVar3 are the ODBC application-specific local variables of INTEGER data type and these contain certain values such as input data while sending the request and output data while retrieving the results.
The second argument in the SQLBindParameter() is the “?” number ordered sequentially from left to right, starting at 1.
Retrieving output parameter value:
The values of INOUT and OUT parameters need to be retrieved from the response by directly printing the local variables (that were bound using SQLBindParameter) after a SQLFetch API call, or by using the SQLBindCol ODBC SDK API followed by SQLFetch API call.
SQLBindCol(..., 1, ..., AppVar1, ..., ...);
SQLBindCol(..., 2, ..., AppVar2, ..., ...);
where:
the second argument in the SQLBindCol() is the parameter number of result data, ordered sequentially from left to right, starting at 1.
The values of INOUT and OUT parameters also can be retrieved using SQLFetch ODBC SDK API followed by SQLGetdata API.
If the procedure has output parameters and OutPutAsResultSet is set to Yes, then the application needs to call SQLMoreResults after the first SQLFetch to retrieve the dynamic result sets. For example:
Note: Using the OutputAsResultSet option is not recommended, because it is deprecated. For details, see “DSN Option to Return Output Parameters as ResultSet” on page 165.
SQLFetch() // Fetch output parameters
if (SQLMoreResults() == SQL_SUCCESS)
do
{
// Process each dynamic result set.
} while (SQLMoreResults == SQL_SUCCESS)
If OutputAsResultset is set to No, then the first fetch and call to MoreResults is not needed.