Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2012
    Posts
    2

    Get Stored Procedure result set within a User Defined Function

    I would like to call a stored procedure inside of a user defined function and sum the results of 1 column returned from the Stored Proc. Is this possible? What is the syntax?

  2. #2
    Join Date
    Dec 2001
    Posts
    1,075
    You should be able to execute the stored procedure using exec.
    Can you give a little more detail, with psuedo-code if possible, about what you are trying to do?
    Also, what exact version of PSQL are you using?
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Nov 2012
    Posts
    2
    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;

    ----------------------

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •