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.

asked 03 Feb '12, 09:19

csfi_jian's gravatar image

csfi_jian
16233
accept rate: 0%


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.

permanent link

answered 03 Feb '12, 09:29

Mark%20Culp's gravatar image

Mark Culp
23.2k9132273
accept rate: 40%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×106

question asked: 03 Feb '12, 09:19

question was seen: 1,040 times

last updated: 03 Feb '12, 09:29