Procedure with multiple return values

[bjanker77]

Hi.

I've started to look into procedures to handle complex calculations, rather than using PowerBuilder code. In one case, I need more than one return value from my procedure, and I have added those parameters with "OUT" in the list:

Ex.

spw_calc_cost (IN Foo integer, OUT storage numeric(13,3), OUT packing numeric(13,3), OUT freight numeric(13,3))

So the question is, can I use these values in three separate columns in my select?

SELECT ColA, ColB, spw_calc_cost(ColC, ColD, ColE), ColF = ColC + ColD + ColE
FROM TableA
WHERE foo = 'bar';

Wanted result:

ColA    ColB    ColC    ColD    ColE    ColF
Foo     Bar    1.000    2.000   3.000   6.000

Or do I have to write 3 separate functions to achieve this? Or is the solution to create a RESULT return type perhaps?

regards,

Bjarne Maritech Systems AS Norway

[Breck_Carter]

To reinforce what Volker said, the SELECT ... FROM procedure-name ( argument-list ) is an incredibly powerful technique for many reasons, not the least of which is the fact you can push an unlimited amount of complexity down into the procedure where it is executed inside the SQL Anywhere engine, thereby taking full advantage of the query optimizer.

In particular, it eliminates Number 21 "Do joins in the application" from the listicle How To Make SQL Anywhere Slow.