The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

# How to use Out or INOUT Variables

 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! asked 15 Sep '10, 03:28 Siger Matt 3.1k●48●64●93 accept rate: 13% 1 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. (15 Sep '10, 09:21) Breck Carter 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? (15 Sep '10, 18:38) Siger Matt

 [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. answered 15 Sep '10, 07:22 Volker Barth 29.3k●287●438●644 accept rate: 32% 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? (15 Sep '10, 18:37) Siger Matt 1 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. (16 Sep '10, 15:03) Breck Carter @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. (16 Sep '10, 15:23) Volker Barth @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. (16 Sep '10, 16:01) Siger Matt
 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; `````` answered 16 Sep '10, 14:43 Volker Barth 29.3k●287●438●644 accept rate: 32% Comment Text Removed
 (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. answered 16 Sep '10, 15:54 Siger Matt 3.1k●48●64●93 accept rate: 13% It was asked before and I did not answer -- I am using SQL Anywhere version 11.0.1.2472 (16 Sep '10, 15:56) Siger Matt 1 In SQL Anywhere, procedures are called by "CALL ...", not EXECUTE (as in Transact-SQL). EXECUTE is for dynamic SQL execution. (16 Sep '10, 15:58) Volker Barth Beautiful. That was the problem. Thank you again! (16 Sep '10, 16:11) Siger Matt
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

question asked: 15 Sep '10, 03:28

question was seen: 1,840 times

last updated: 01 Oct '10, 14:53