Returning result sets from stored procedures

[Former Member]

In addition to returning output parameters, a stored procedure can return a result set (that is, a result table associated with a cursor opened in the stored procedure) to the application that issues the CALL statement.

Can somebody show me the syntax on how to fetch the result set from a store procedure based on the returning parameter (for example error_code=0, then fetch result set, if error_code !=0, do nothing). I need to do this in ESQL.

Thanks.

[MarkCulp]

AFAIK What you are asking for cannot be done.

The issue is that the output parameters are returned from the procedure to the calling context when the procedure has completed its execution - i.e. after it has returned all of the result set(s) to the client. Therefore at the time that the client accepts/reads the result set(s) the output parameters are not available.

Note that the way that SQL Anywhere executes procedures and handles cursors/result sets is different than other RDBMSes - other databases will generate and materialize all of the result set(s) and return the output parameters and result set(s) to the client before proceeding pass the call within the client. The drawback to this process (of other databases) is that the entire result set is materialized so if the client only reads a few rows from the cursor and then closes it then there is a terrible waste of effort.