Sybase Adaptive Server Anywhere - Page 473

Chapter 9 SQL Statements
455
Some procedures can more than one result set, with different numbers of
columns, depending on how they are executed. For example, the following
procedure returns two columns under some circumstances, and one in others.
CREATE PROCEDURE names( IN formal char(1))
BEGIN
IF formal = ’n’ THEN
SELECT emp_fname
FROM employee
ELSE
SELECT emp_lname,emp_fname
FROM employee
END IF
END
Procedures with variable result sets must be written without a RESULT
clause, or in Transact-SQL. Their use is subject to the following limitations:
♦
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. The CURSOR cursor-name
clause on the DESCRIBE statement is required.
♦
ODBC Variable result-set procedures can be used by ODBC
applications. The proper description of the result sets is carried out by
the ODBC driver.
♦
Open Client applications Variable result-set procedures can be used
by Open Client applications.
If your procedure returns only one result set, you should use a RESULT
clause. The presence of this clause prevents ODBC and Open Client
applications from re-describing the result set after a cursor is open.
In order to handle multiple result sets, ODBC must describe the currently
executing cursor, not the procedure’s defined result set. Therefore, ODBC
does not always describe column names as defined in the RESULT clause of
the procedure definition. To avoid this problem, use column aliases in the
SELECT statement that generates the result set.
ON EXCEPTION RESUME clause This clause enables Transact-SQL -
like error handling to be used within a Watcom-SQL syntax procedure.
If you use ON EXCEPTION RESUME, the procedure takes an action that
depends on the setting of the ON_TSQL_ERROR option. If
ON_TSQL_ERROR is set to CONDITIONAL (which is the default) the
execution continues if the next statement handles the error; otherwise, it
exits.
Error-handling statements include the following:
♦ IF