Create a file called spExample.rmd containing the following code:
{begin code spExample.rmd}
{ FileName spExample.rmd
clear this system variable "stp_return" ALWAYS
as it can hold different datatypes
}
CLEAR VAR stp_return
SET VAR presult INTEGER = NULL
IF p1 IS NOT NULL AND p2 IS NOT NULL THEN
SET VAR presult = (.p1 * p2)
ELSE
SET VAR pResult = NULL
ENDIF
Clear var p1, p2
RETURN .pResult
{end code spExample.rmd}
From the R> of a "Connected" database, execute the following:
put spExample.rmd as spExample p1 integer, p2 integer return integer
The previous line injects the file spExample.rmd into the SYS_PROCEDURES
system table and configures the procedure to recieve TWO arguments or
parameters, (p1 and p2) of the datatype Integer and RETURNing an Integer as
the Result.
In the Stored procedure, the Result is Assigned to the system variable
"stp_return". In the example, the multiplication of the two variables p1 by
p2 are assigned to variable pResult and at the same time "stp_return".
When we execute the following, either at the command line R> or within a
code segment:
set var vAnswer integer = (call spExample(200, 11))
the two parameters, 200 and 11 are sent to the stored procedure which
processes it as described above. The Assignment of the result to the system
variable "stp_return" makes the value available to the CALL command to pass
to its' placeholder in an expression as shown by the above set var vAnswer.
Since it is a placeholder, it can also be used anyplace you can use an
expression as in a Select command, but I would not use a Stored Procedure in
this way against a very large Select as the round trip per line of the
select would invoke a penalty on the speed, but you can do something like
this with the example stored procedure.
Select (Call spExample(Col1, Col4)) into vIntegerResult ind Vin0 from
SomeTable where ID Col = 'SomeUniqueValue'
HTH
----- Original Message -----
From: "Luc Delcoigne" <[email protected]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Friday, June 11, 2010 2:52 AM
Subject: [RBASE-L] - Re: setting a form variable with a stored procedure
This solution is not so evident for me.
Karen's solution works fine, but I'd like to have some more info on your
solution for the future.
Can you give me an example in extenso ?
Thank you very much, Mike.
Luc Delcoigne
--------------------------------------------------
From: "Mike Byerley" <[email protected]>
Sent: Friday, June 11, 2010 12:32 AM
To: "RBASE-L Mailing List" <[email protected]>
Subject: [RBASE-L] - Re: setting a form variable with a stored procedure
Design your stored procedure to return a result and assign the result
directly to the variable Like:
set var vTheVarToSet SomeDataType = (Call StoredProcedureName(Arg1,
Arg2))
If the Form Field you want to set is not a variable field, then use the
Property command to set the field value to the variable returned by the
stored procedure.
----- Original Message -----
From: "Luc Delcoigne" <[email protected]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Thursday, June 10, 2010 5:38 PM
Subject: [RBASE-L] - setting a form variable with a stored procedure
Hi,
On a form I have multipele currency fields.
Via a stored procedure I calculate the sum of certain fields (which
varies) and want to display the result of my stored procedure in a
variable (or other?) field on the form.
In access you can set the source/value property of a variable to "
=Procedure()".
In rbase I have a stored procedure that calculates the variable
"vpricetopay".
How can I set a variable to display this result dynamically on my form
without saving the value to a table ?
Tx
Luc Delcoigne
