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? asked 09 Jan '11, 21:25 Breck Carter |
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.
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
and the caller of q() did not consume the result set generated by p(), for example:
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 Awesome! The Rule is upheld! @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? 1
@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. @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:) 1
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. |
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:)