Hi:

Just tested with Stored Proc. Some problem about what should be
returned.

Any help appreciated.

SET TERM ## ;
CREATE PROCEDURE NETMEMBERS (CUTDATE DATE, ID INTEGER)
RETURNS (NetMembers INTEGER)
AS
DECLARE VARIABLE ActiveMembers INTEGER;
DECLARE VARIABLE CancelledMembers INTEGER;
BEGIN
BEGIN
SELECT COUNT (ASSOCID)
FROM ASSOC_C
WHERE INITL_DATE <= :CutDate AND ID = :ID
INTO ActiveMembers;
END
BEGIN
SELECT COUNT (ASSOCID)
FROM ASSOC_C
WHERE CANCL_DATE < :CutDate AND ID = :ID
INTO CancelledMembers;
END
NetMembers = ActiveMembers - CancelledMembers;
EXIT;
END ##
SET TERM ; ##

The MetaData:

COMMIT WORK;
SET AUTODDL OFF;
SET TERM ^ ;

/* Stored procedures */

CREATE PROCEDURE "NETMEMBERS"
(
CUTDATE DATE,
ID INTEGER
)
RETURNS
(
NETMEMBERS INTEGER
)
AS
BEGIN EXIT; END ^


ALTER PROCEDURE "NETMEMBERS"
(
CUTDATE DATE,
ID INTEGER
)
RETURNS
(
NETMEMBERS INTEGER
)
AS

DECLARE VARIABLE ActiveMembers INTEGER;
DECLARE VARIABLE CancelledMembers INTEGER;
BEGIN
BEGIN
SELECT COUNT (ASSOCID)
FROM ASSOC_C
WHERE INITL_DATE <= :CutDate AND ID = :ID
INTO ActiveMembers;
END
BEGIN
SELECT COUNT (ASSOCID)
FROM ASSOC_C
WHERE CANCL_DATE < :CutDate AND ID = :ID
INTO CancelledMembers;
END
NetMembers = ActiveMembers - CancelledMembers;
EXIT;
END
^

SET TERM ; ^
COMMIT WORK;
SET AUTODDL ON;

And the execute:

EXECUTE PROCEDURE NETMEMBERS '09/30/2000', '685';

This returns NULL whereas it should return 47 as in the normal
Select Count below:

(The ASSOCID is used below to just return one records v the same
result listed as many times as there are records-clarification)

SELECT ASSOCID,

((SELECT COUNT (A1.ASSOCID)
FROM ASSOC_C A1
WHERE (A1.INITL_DATE <= '09/30/2000')) -

(SELECT COUNT (A2.ASSOCID)
FROM ASSOC_C A2
WHERE (A2.CANCL_DATE < '09/30/2000'))) AS NetMembers

FROM ASSOC_C A0
WHERE A0.ASSOCID = 1 AND A0.ID = 685;