How to call a stored procedure from a function

Question

I want to call a stored procedure from a user defined function in pervasive sql. I have:

SET :retVal = Sales_SP_getNetSales('2013-1-1','2013-1-31','05',:NetSales);

as a call in my function where Sales_SP_getNetSales is the stored procedure. Is this the correct way of doing it? The stored procedure returns an integer.

Answer 1

I can't find any way to call a Stored Procedure from a UDF. Running a Stored Procedure requires either the EXEC(UTE) or CALL statement. There is, however, no way to translate the result set returned by the Stored Procedure to a variable in a Pervasive UDF. Part of this may be because a Stored Procedure cannot be guaranteed to return only one value. A Stored Procedure, in Pervasive, returns a whole result set.
You might consider changing the Stored Procedure to a Function. Calling a function from a function is possible:

create function f2(:B integer)
returns integer
as  
begin
return :B;
end;

CREATE FUNCTION f1(:A integer)
RETURNS Integer
AS
BEGIN 
RETURN f2(:a);
END;

select f1(5)