Using a stored procedure with variable result sets in the FROM clause

[VolkerBarth]

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.

[VolkerBarth · Accepted solution]

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...

[Breck_Carter · Accepted solution]

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

[justin_willey · Accepted solution]

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

[VolkerBarth · Accepted solution]

Even necessary for procedures with variable result sets... 🙂