I had a situation recently where I wanted to multiply an unknown number of percents within a procedure and then multiply that result by a price and put the result into a variable and then when it was all finished, a field in a row would be set = to the variable.
The idea being that depending on the situation there could be 3 numbers, 5 numbers, or 20 numbers.
Let's say the situation is that I have a bunch of items, and I also have a bunch of coupons for a % off of the items. You can combine the coupons, so to compute the price you need the total effect of the coupons multiplied by the price.
There are probably many ways to do this, and this is hardly the best way, but the answer I am after is specifically how to get the out variable from the multiply function into the parent compute function.
Here is the table of items.
Here is the table of coupons.
Here is the Multiply Procedure
Here is the Compute Procedure
So I want to use the EFac variable in Compute as the Out variable for Multiply.
When I run it I get "Function multiply has invalid parameter 'Res1'('OUT')
Please explain how to use the Out variable correctly.
[Sorry, no real answer - just a few observations. But a comment seems inappropriate for formatting reasons.]
What version and build do you use?
Your Multiply procedure looks somewhat strange to me, as it uses both an out parameter and a identical return value. That's an an obvious redundancy. Whereas return values are used with functions, inout/out parameters (and optional result sets) are typical for procedures. So your procedure seems to mix up these features a bit – something that older SA versions tend to accept whereas newer ones will prevent.
As you basically want to use a simple mathematical function, I would recommend to use a function like:
Using a function will additionally make the use inside a second procedure easier (and more comprehensible).
I still don't get the exact requirements (as said, treat this as mere remarks), but usually you can use a cursor loop when you have to call a procedure/function repeated times and each call has to be based on the previous call's return value.
(The situation is different when the calls of the procedure/function are independent of each other. Then you could simply embed a function call in a select list of a query or use the procedure with a so-called lateral procedure call in the FROM clause of a query.)
So inside your procedure Compute (again turned into a function) you might use a FOR statement as a very convenient cursor loop:
Note: All statements are untested but should give you a hint.
Additional answer for procedures with several out parameters (cf. Siger's comments):
Say, you need to compute an integer division and would like to get both the quotient and the remainder in one call. That cannot be done with a function, and so you might use something like the following:
You would call that procedure in another procedure similar to the following block (note: the declare with initialization is new in a V12). Obviously, you have to declare variables in the outer procedure to use them as arguments for the out parameters ("Call by reference" in PL terms).
answered 16 Sep '10, 14:43
(Not an answer, just needed the code formatting)
That makes absolute sense and it was how I thought it should work. But even when I strip down my example to match yours to the following:
I still get the error when I run compute...
I really appreciate the help.
answered 16 Sep '10, 15:54