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. |
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).
|
First of all, multiply should be created as a FUNCTION because that is how it is used. Second, functions have "RETURNS" values, not OUT parameters: Create function multiply (in mult1 int, in mult2 int) returns int begin... Third, there may be other issues, like the interesting use of COUNT, but you wanted a specific answer to the OUT error.
Yes that makes sense. If the example was more complicated and returned multiple things to OUT Parameters, what would be the correct way to have them come out of the one procedure to be used in the parent procedure?