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%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

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.

(09 Jan '11, 23:03) Ron Hiner

How are you call procedure q?

(10 Jan '11, 07:58) Mark Culp

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

(10 Jan '11, 10:23) Breck Carter

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

(12 Jan '11, 12:03) Volker Barth

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?

permanent link

answered 10 Jan '11, 08:44

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297
accept rate: 41%

Awesome! The Rule is upheld!

(10 Jan '11, 10:24) Breck Carter

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

(10 Jan '11, 20:21) Volker Barth
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.

(11 Jan '11, 00:50) Mark Culp

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

(11 Jan '11, 10:30) Volker Barth
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.

(11 Jan '11, 23:21) Mark Culp
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: 09 Jan '11, 21:25

question was seen: 2,075 times

last updated: 10 Jan '11, 08:44