Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 271 / 536

CHAPTER 6 Using Procedures and Batches
251
If you are not using this feature of variable result sets, it is recommended that
you employ a RESULT clause, for performance reasons and to allow front-end
tools to discern the columns and data types the procedure will produce without
executing it.
For example, the following procedure returns two columns if the input variable
is Y, but only one column otherwise:
CREATE PROCEDURE names( IN formal char(1))
BEGIN
IF formal = ’y’ THEN
SELECT emp_lname, emp_fname
FROM employee
ELSE
SELECT emp_fname
FROM employee
END IF
END
The use of variable result sets in procedures is subject to some limitations,
depending on the interface used by the client application.
• Embedded SQL
You must DESCRIBE the procedure call after the
cursor for the result set is opened, but before any rows are returned, in
order to get the proper shape of result set.
For information about the DESCRIBE statement, see “DESCRIBE statement”
in Adaptive Server IQ Reference Manual.
• ODBC Variable result set procedures can be used by ODBC
applications. The proper description of the variable result sets is carried
out by the Adaptive Server IQ ODBC driver.
• Open Client applications
Variable result set procedures can be used
by Open Client applications. The proper description of the variable result
sets is carried out by Adaptive Server IQ.
• DBISQL DBISQL does not support variable result set procedures, and
so cannot be used for testing this feature.
Using cursors in procedures
Cursors are used to retrieve rows one at a time from a query or stored procedure
that has multiple rows in its result set. A cursor is a handle or an identifier for
the query or procedure, and for a current position within the result set.