If I have a stored procedure with variable result sets (and therefore no RESULT clause), what are the requirements to use that in the FROM clause?

Background: I have a stored procedure SP1 that returns different result sets based on one particular input parameter, kind of a "test" flag that when set selects several result sets and otherwise just one (all with different schemas).

AFAIK the system catalog (i.e. system view SYS.SYSPROCPARMS) does store the result set returned by the first SELECT statement in the procedure's body. In my case, that one belongs to the "test" result sets.

Now, another procedure SP2 calls that procedure within a SELECT FROM ... and does not set that "test" flag.

According to the missing RESULT clause and the fact that the result set stored in the system catalog is different from the one I'm expecting here, I certainly have to provide the expected schema via a fitting WITH clause as part of the procedure-call, i.e. something like

   SELECT *
   FROM SP1(...) WITH ([expected result set description]) AS SP

While this has worked several times, now I do get a SQLCODE -866 ("%1 returned a result set with a different schema than expected") error.

I cannot resolve this via recompiling or altering the according procedures.

Have I somehow missed the point, or are there further requirements to take care of?

I'm still using v16.0.0.2798.

asked 23 Jun '20, 10:52

Volker%20Barth's gravatar image

Volker Barth
39.9k360547817
accept rate: 34%

edited 23 Jun '20, 10:54


Oops, my fault, after re-reading and re-testing I noticed there was a further SELECT (which I meant to be a SELECT INTO and was therefore not expecting a result set) that really did generate a result set, and so the returned error was very appropriate.

Resume: The procedure call works as expected with the fitting WITH clause, and I'd better take care of really reading my code...

permanent link

answered 23 Jun '20, 11:37

Volker%20Barth's gravatar image

Volker Barth
39.9k360547817
accept rate: 34%

edited 23 Jun '20, 11:39

There's no "thrill of the chase" in a code review of one's own code :)

(23 Jun '20, 13:41) Breck Carter

I had never noticed the WITH clause - very handy. Thank you.

(24 Jun '20, 14:53) Justin Willey
Replies hidden

Even necessary for procedures with variable result sets... :)

(24 Jun '20, 15:45) Volker Barth
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:

×260
×125
×20

question asked: 23 Jun '20, 10:52

question was seen: 942 times

last updated: 24 Jun '20, 15:45