What happens when a procedure RESULT set is ignored?

[Breck_Carter]

Let's say you have a well-tested procedure p that (a) does some useful work and then (b) returns a RESULT set... say it's normally called by an HTML web service:

CREATE PROCEDURE p() RESULT ( x LONG VARCHAR ) BEGIN
   [useful work]
   SELECT 'Hello, World!' ;
END;

Now let's say you're coding a new procedure q where you need to have the useful work performed but you have no need of the result set. Other than wasting some time and resources to produce the result set, is there any further penalty to just calling p and ignoring the result set?

CREATE PROCEDURE q() BEGIN
   CALL p();
END;

There's no [gasp] memory leak or anything else that would violate the rule "Watcom Does Things The Way They Should Be Done", right?

The result set just gets tossed, right?

[Former Member]

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.

[MarkCulp]

How are you call procedure q?

[Breck_Carter]

@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 🙂

[VolkerBarth]

@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:)

[MarkCulp · Accepted solution]

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?

[Breck_Carter · Accepted solution]

Awesome! The Rule is upheld!

[VolkerBarth · Accepted solution]

@Mark: So the general recommendation is to place the final select statement(s) at the end of the procedure's body, right? - Besides that, is this behaviour one of the reasons why SA doesn't support procedures with both output values/return values and result sets?

[MarkCulp · Accepted solution]

@Volker: (1) I guess if you think that there is a possibility that the caller is not going to consume the result set then it would be wise to do any "important" work before you generate the result set. (2) Yes, this behaviour is one of the reasons why it is difficult to support both returning result sets and output parameters.

[VolkerBarth · Accepted solution]

@mark: (1) Yes, that's my intention. Now and then, us users also try to struggle to apply to the "Doing Things The Way They Should Be Done" rule:)

[MarkCulp · Accepted solution]

I should also note that if you are using web services then it is definitely important for you to make the SELECT statement (that generates the result set which is returned as the response to the web service request) to be the last statement in your web service stored procedure since (with the current implementation) no statement in your procedure after this SELECT statement will be executed once the HTTP response has been sent.

[VolkerBarth · Accepted solution]

Yes, that's a very late comment...:)

Aargh, I stumbled over that again:

I had just created a stored procedure with several result sets and a final INSERT statement, and when simply calling the procedure within DBISQL or within another procedure the INSERT was executed as expected. However, when using cursor loops to fetch the result sets with RESUME calls inbetween, the INSERT was ignored. I would either have to add a final RESUME call or better place the INSERT within the procedure BEFORE returning any result sets.

So another big thanks for those great and helpful information, Mark!