How to use Out or INOUT Variables

[former_SQLA_member1694868]

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.

CREATE TABLE AAITEM (AAItemID integer, ItemName varchar (20))
INSERT INTO AAItem ("AAItemID","ItemName") VALUES(1,'Book');
INSERT INTO AAItem ("AAItemID","ItemName") VALUES(2,'Bike');
INSERT INTO AAItem ("AAItemID","ItemName") VALUES(3,'Basket');

Here is the table of coupons.

CREATE TABLE AACoupons (AACouponID integer, CouponAmount integer, AAItemID integer)
INSERT INTO AACoupons ("AACouponID","CouponAmount","AAItemID") VALUES(100,5,1);
INSERT INTO AACoupons ("AACouponID","CouponAmount","AAItemID") VALUES(101,15,1);
INSERT INTO AACoupons ("AACouponID","CouponAmount","AAItemID") VALUES(102,25,1);
INSERT INTO AACoupons ("AACouponID","CouponAmount","AAItemID") VALUES(200,3,2);
INSERT INTO AACoupons ("AACouponID","CouponAmount","AAItemID") VALUES(201,6,2);
INSERT INTO AACoupons ("AACouponID","CouponAmount","AAItemID") VALUES(202,9,2);
INSERT INTO AACoupons ("AACouponID","CouponAmount","AAItemID") VALUES(300,2,3);
INSERT INTO AACoupons ("AACouponID","CouponAmount","AAItemID") VALUES(301,4,3);
INSERT INTO AACoupons ("AACouponID","CouponAmount","AAItemID") VALUES(302,6,3);

Here is the Multiply Procedure

Create procedure multiply (in mult1 int, in mult2 int, out Res1 int Default 1)
begin
Set res1 = mult1 * mult2;
return (res1);
end;

Here is the Compute Procedure

create procedure Compute (inID integer)
begin
declare EFac integer;
declare @MattT integer;
declare retval integer;
set EFac=1;

select count
(multiply (AACoupons.CouponAmount, EFac, EFac)) into retval
from AACoupons
where AAItemID = InID;

Update  FinalPrice
set CouponAmount = EFac
end

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.
Thank you!

[Breck_Carter]

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.

[former_SQLA_member1694868]

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?

[VolkerBarth · Accepted solution]

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

create function multiply (in mult1 int, in mult2 int) returns int
begin
   declare res1 int;
   -- return value defaulting to 1 in case of NULL values as input
   Set res1 = isnull(mult1 * mult2, 1);
   return (res1);
end;

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:

create function Compute (inID integer) returns int
begin
   declare EFac integer;
   declare retval integer;

for forLoop as crs cursor for
      select CouponAmount from AACoupons where AAItemID = InID order by AACouponID
      for read only
   do
      if EFac is null then
         set EFac=1;
      end if;
      set EFac = Multiply(CouponAmount, EFac);
   end for;

-- further computation (counting or whatever)...
   set retval = ...;
   return (retval);
end;

Note: All statements are untested but should give you a hint.

[former_SQLA_member1694868 · Accepted solution]

Absolutely, This gives many hints for the future. If the example was more complicated, say with multiple return values so a function would not work anymore, what is the correct way to bring the OUT variable from the one procedure into the parent procedure?

[Breck_Carter · Accepted solution]

A procedure which returns a result set can be called just like a table in the FROM clause, and thus you can reference multiple columns from the (single?) returned row in the SELECT list etcetera. SELECT * FROM proc_name ( in_parameters ) is EXTREMELY powerful and valuable.

[VolkerBarth · Accepted solution]

@Breck: Whereas Siger explicitly asked for several out parameters (and so I tried to answer that), I strongly agree that a resultset with several rows is much easier to use, particularly when not called from another procedure but from some client application.

[former_SQLA_member1694868 · Accepted solution]

@Breck: That is indeed helpful and I had not considered using it that way. I will use that construction for something in the future for sure. Thank you.

[VolkerBarth · Accepted solution]

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:

create procedure int_division(in dividend int, in divisor int,
   out quotient int, out remainder int)
no result set
begin
   -- Note: error handling (e.g. divide by zero) is stripped for simplicity
   set quotient = dividend / divisor;
   set remainder = dividend % divisor;
end;

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

begin
   declare d1 int = 21;
   declare d2 int = 11;
   declare q int;
   declare r int;
   call int_division(d1, d2, q, r);
   select d1, d2, q, r;
end;

[former_SQLA_member1694868]

(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:

create procedure multiply (in mult1 int, in mult2 int, out Res1 int Default 1)
no result set
begin 
Set Res1 = mult1 * mult2;
end; 

//==========================================================

create procedure Compute (inID integer) 
begin 
declare OUT1 integer;
declare IN1 integer; 
declare IN2 integer; 
set IN1 = 5; 
set IN2 = 2; 
execute (multiply (IN1, IN2, OUT1));
Select OUT1; 
end; 

I still get the error when I run compute...

I really appreciate the help.

[former_SQLA_member1694868]

It was asked before and I did not answer -- I am using SQL Anywhere version 11.0.1.2472

[VolkerBarth]

In SQL Anywhere, procedures are called by "CALL ...", not EXECUTE (as in Transact-SQL). EXECUTE is for dynamic SQL execution.

[former_SQLA_member1694868]

Beautiful. That was the problem. Thank you again!