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

[VolkerBarth · Accepted solution]

Note, the title is not really appropriate, stored procedures may have one single return value (returned via the RETURN statement) but you are using multiple output parameters...

Stored procedures are called via the CALL statement, so the attempt to use them in a SELECT list should fail.

In case your procedure does return a result set (instead of using several output parameters), it can also be used within a FROM clause, such as

CREATE PROCEDURE spw_calc_cost (IN Foo integer)
RESULT (storage numeric(13,3), packing numeric(13,3), freight numeric(13,3))
BEGIN
...
   SELECT ... -- selecting your 3 calculated values
END;

SELECT ColA, ColB, sp.storage, sp.packing, sp.freight, ColF = sp.storage + sp.packing + sp.freight
FROM spw_calc_cost(x) sp;

[bjanker77 · Accepted solution]

Hi!

Yes, this works very good. Thanks. But what if I need these values in select containing other columns from other tables? Ex. select ItemNo, Description,<storage>,<packing>,<freight> from Items where foo = 'bar';

I suppose I have to use subselects, like this?

select ItemNo, Description, (select storage from spw_calc_cost(x)) as storage, (select packing from spw_calc_cost(x)) as packing, (select freight from spw_calc_cost(x)) as freight from Items where foo = 'bar';

Regards,

Bjarne

[VolkerBarth · Accepted solution]

No, you can just add other tables to the FROM clause.

What is the connection between these other tables and the SP? Is the SP based on some column value from other tables (say here, dependent on the ItemNo), or will the SP result set be fully joined with the other tables?

Here's a variant with a CROSS APPLY operator, handy if the SP's input parameter is based on a column value (here I assume the ItemNo):

select ItemNo, Description, storage, packing, freight
from Items cross apply spw_calc_cost(Items.ItemNo) sp
where foo = 'bar';

Note, the according dop topic provides more samples...

[bjanker77 · Accepted solution]

Exactly what I was looking for!

In short, we have a query today with a couple of columns which is created in the PowerBuilder datawindow with "cast(null as numeric(13,3)) as X". After retrieve of the data, we loop through all the rows in the datawindow to calculate values for all those null-columns. It worked OK before, but now the amount of data has multiplied by the 100's, so is's very slow approach. But when we do the calculation in the database using a procedure, things are speeding up quite a lot.

Thanks! 🙂

Bjarne

[VolkerBarth · Accepted solution]

So feel free to mark the answer as "accepted", said the Forum Nanny:)

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

[t1950]

This isn't an answer to your question, just a tip for programming in PB. Whether the dw is visible or not, until the calculations are completed set redraw off (dw.SetRedraw( False ). Once the processing is finished, turn redraw back on (dw.SetRedraw( True ) ). It's 100's of times faster.

[bjanker77]

Hi.

We are using setdraw(false) before the processing starts, but the calculation i PowerScript is very slow still. That´s why I want to use a database procedure on retrieve.

Thanks.

Bjarne