Sybase 12.4.2 manual Using cursors in procedures, 251

CHAPTER 6 Using Procedures and Batches

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.

251