The answer to "The result set just gets tossed, right?" is that yes, it may be tossed, but better yet the result may actually never get generated.
Here is how SQL Anywhere works w.r.t. handling of results sets.
- a procedure is executed up to the point that a result set is generated at which point the procedure pauses
- the calling procedure then needs to "consume" the result set
- once the calling procedure consumes all of the rows from the result set (or "resumes" past the result set) then the called procedure (which generated the result set) proceeds past the point in which the result set is generated
- if the result set is not consumed and the cursor (that has been opened either explicitly by the user or implicitly by SA) is closed then the called procedure is terminated without outputting the result set and everything is cleaned-up
In your simple example the caller of procedure q will be required to consume the result set before q() will continue past the call to procedure p().
So for example, lets say we had
CREATE PROCEDURE q() BEGIN
CALL p();
MESSAGE 'got past CALL p() in procedure q' to CONSOLE;
END;
and the caller of q() did not consume the result set generated by p(), for example:
BEGIN
DECLARE crsr CURSOR USING 'SELECT * FROM q()';
OPEN crsr;
CLOSE crsr;
END;
then the message in q() will never be written to the console because procedure p() was terminated at the point that the result set was (or would have been) generated. Any work performed in p() prior to the SELECT 'Hello World!' will be have been performed and will persist provided that the transaction is committed.
Note that SQL Anywhere's behaviour w.r.t. handling of result sets is different than other RDBMSes. Other database products - such as ASE and MSSQL - completely execute all procedure logic and generates all result sets (which may consist of many many rows) and returns these results sets back to the caller (e.g. client application) to deal with. i.e. The client does not continue past the point of the call to the procedure until the procedure has fully executed everything it was to do and has returned (fallen off the bottom of the procedure or executed a RETURN statement).
When a client calls a SQL Anywhere procedure, SA only executes to the point where the first result set gets generated, at which point the client continues past the call and may fetch the rows. When the SA client does a RESUME, the procedure continues executing past the point where the first result set was generated and will pause again when/if the procedure generates a second result set. Eventually either the SA procedure runs off the bottom (of the procedure) or does an explicit return and/or the client closes the cursor over which the procedure was called (at which point the procedure context is closed and cleaned-up).
HTH?
answered
10 Jan '11, 08:44
Mark Culp
24.8k●9●139●295
accept rate:
41%
If I don't need a result set, I create a function. To me, the difference between a function and a procedure is that a procedure wants to return a result set, and a function does not.
How are you call procedure q?
@Ron: The point here is that p() exists and must return a result set in other contexts (web service etc). What I want to do is re-use p() in a different context that does not need the result set. I do not want to modify p() in any way... it works :)
@Breck: Thanks for another question that looked quite simple but lead to a whole bunch of useful explanations (which are not officially documented, AFAIK). - This site is great:)