64 bit server engine: ntdbsmgr64.exe file verion 11.20.15.0;
Here is the stored procedure parms and columns returned:
----------------
CREATE PROCEDURE GN_sp_getDepositsAndInterest
(
in :company varchar(25),
in :jobnumber varchar(25),
in :interestrate double
)
RETURNS
(
JobNumber varchar(25),
Closing_Date_Planned date,
State char(2),
DepositAmount double,
DepositedDate date,
InterestRate double,
NumberDaysOfInterest double,
DepositAmtEligible double,
InterestAmount double
);
----------------------------------
I need to create a scalar function that calls the stored procedure, sums the last column and returns that value as a double.
Here's what I came up with, but it fails:
Create Function GN_fn_getTotalInterest
(
in :company varchar(25),
in :jobnumber varchar(25),
in :interestrate double
)
RETURNS double
AS
BEGIN
DECLARE :totint double;
//SET :totint = (select("INFOSYS".GN_sp_getDepositsAndInterest(:co mpany, :jobnumber, :interestrate)));
//return (select("INFOSYS".GN_sp_getDepositsAndInterest(:co mpany, :jobnumber, :interestrate)));
SET :totint = (SELECT SUM(InterestAmount) FROM exec GN_sp_getDepositsAndInterest(:company, :jobnumber, :interestrate);
RETURN :totint;
----------------------