Calling a Stored Procedure in a UDF

it can't be done
what exactly does you stored procedure do?
why not doing it in the function?

My Procedure returns a result set. Here is the code of my stored procedure.
 Can i turn my procedure into a function? Please advise.

CREATE PROCEDURE CHESSIE.SP_REM (IN VI_CLIENT_ID INTEGER,IN AD_START_DT DATE, IN AD_END_DT DATE  )
------------------------------------------------------------------------
------------------------------------------------------------------------


P1: BEGIN
--DECLARE VI_CLIENT_ID INTEGER;
DECLARE VD_REMOVAL_DT DATE;
DECLARE VD_REMOVAL_DT_OLD DATE;
DECLARE VD_RETURN_DT DATE;
DECLARE VD_RETURN_DT_OLD DATE;
DECLARE VN_CLNT_CNT INTEGER;
DECLARE VN_CLNT_STG_CNT INTEGER DEFAULT 0;
DECLARE VN_REM_STG_CNT INTEGER DEFAULT 0;
DECLARE VN_RET_STG_CNT INTEGER DEFAULT 0;
DECLARE VN_REM_CNT INTEGER DEFAULT 0;
DECLARE VS_CIS_ID VARCHAR(10);

DECLARE REM_CUR CURSOR WITH HOLD FOR
SELECT  REMOVAL_DT,RETURN_DT FROM TB_REMOVAL
WHERE CLIENT_ID = VI_CLIENT_ID
AND DELETE_SW = 'N'
ORDER BY REMOVAL_ID;

DECLARE CLNT_CUR CURSOR WITH HOLD FOR
SELECT DISTINCT AF.CLIENT_ID,TC.CIS_CLIENT_ID FROM TB_REMOVAL AF
JOIN TB_CLIENT TC ON AF.CLIENT_ID = TC.CLIENT_ID AND TC.CIS_CLIENT_ID IS NOT NULL
WHERE TC.CLIENT_ID = VI_CLIENT_ID
order by af.client_id;

DECLARE GLOBAL TEMPORARY TABLE REMOVALS(V_ID INTEGER GENERATED ALWAYS AS IDENTITY
                                       ,CLIENT_ID INTEGER
                                       ,REMOVAL_DT DATE
                                       ,RETURN_DT DATE
                                       )
 WITH REPLACE;

--DELETE FROM TB_AFCARS_CARES_IN;
--COMMIT;

SET VN_CLNT_CNT = (SELECT COUNT(DISTINCT AF.CLIENT_ID) FROM TB_REMOVAL AF
JOIN TB_CLIENT TC ON AF.CLIENT_ID = TC.CLIENT_ID AND TC.CIS_CLIENT_ID IS NOT NULL
AND AF.CLIENT_ID = VI_CLIENT_ID)   ;


OPEN CLNT_CUR;
        WHILE VN_CLNT_CNT > 0 DO
                FETCH FROM CLNT_CUR INTO VI_CLIENT_ID,VS_CIS_ID;

                        SET VN_REM_CNT = (SELECT COUNT(*) FROM TB_REMOVAL WHERE CLIENT_ID = VI_CLIENT_ID AND DELETE_SW = 'N');
                        OPEN REM_CUR;
                        WHILE VN_REM_CNT > 0 DO

                        FETCH REM_CUR INTO VD_REMOVAL_DT,VD_RETURN_DT;
                                SET VN_CLNT_STG_CNT = (SELECT COUNT(*) FROM SESSION.REMOVALS WHERE CLIENT_ID = VI_CLIENT_ID);
                                SET VN_REM_STG_CNT = (SELECT COUNT(*)FROM SESSION.REMOVALS WHERE CLIENT_ID = VI_CLIENT_ID AND RETURN_DT = VD_REMOVAL_DT);
                                SET VN_RET_STG_CNT = (SELECT COUNT(*)FROM SESSION.REMOVALS WHERE CLIENT_ID = VI_CLIENT_ID AND REMOVAL_DT = VD_RETURN_DT);

                                IF VN_CLNT_STG_CNT = 0 THEN
                                       INSERT INTO SESSION.REMOVALS(CLIENT_ID,REMOVAL_DT,RETURN_DT)
                                       VALUES(VI_CLIENT_ID,VD_REMOVAL_DT,VD_RETURN_DT) ;
                                END IF;

                                IF VN_CLNT_STG_CNT >= 1 AND VN_REM_STG_CNT = 0 AND VN_REM_STG_CNT = 0  THEN
                                           INSERT INTO SESSION.REMOVALS(CLIENT_ID,REMOVAL_DT,RETURN_DT)
                                       VALUES(VI_CLIENT_ID,VD_REMOVAL_DT,VD_RETURN_DT) ;
                                END IF;

                                IF VN_REM_STG_CNT >= 1 AND VD_REMOVAL_DT = VD_RETURN_DT_OLD THEN
                                        UPDATE SESSION.REMOVALS
                                        SET RETURN_DT = VD_RETURN_DT
                                        WHERE CLIENT_ID = VI_CLIENT_ID
                                        AND V_ID = (SELECT V_ID FROM SESSION.REMOVALS
                                                  WHERE CLIENT_ID = VI_CLIENT_ID
                                                  ORDER  BY V_ID DESC
                                                  FETCH FIRST 1 ROW ONLY);
                                END IF;
                               SET VD_REMOVAL_DT_OLD = VD_REMOVAL_DT;
                               SET VD_RETURN_DT_OLD = VD_RETURN_DT;

                             SET VN_REM_CNT = VN_REM_CNT -1;

                         END WHILE;

                         CLOSE REM_CUR;
              SET VN_CLNT_CNT = VN_CLNT_CNT- 1;
              END WHILE;

        CLOSE CLNT_CUR;

P2: BEGIN

DECLARE CUR2 CURSOR WITH RETURN TO CLIENT FOR
SELECT DISTINCT CLIENT_ID,REMOVAL_DT,RETURN_DT
FROM SESSION.REMOVALS;
OPEN CUR2;

END P2;
END P1 

Select allOpen in new window

I'm not able to convert it into a SQL statement. That's why i came up with these compound statements in a loop.

Can you help me convert it to a SQL?

I have been able to create a table type function, returning client_id, removal_dt and return_dt.
 But it works when i pass a value at a time.

select * from table(f_rem(8756)) as removal;

But when i try to use like below, it is saying r.client_id is not valid in the context used.
How do i use the table type function to return result sets for all the client_id s?

select removal.*
from table(f_rem(r.client_id)) as removal,tb_rmt as r
where removal.client_id = r.client_id;

I've requested that this question be closed as follows:

Accepted answer: 0 points for pvsbandi's comment http:/Q_27494319.html#37288339

for the following reason:

I'll ask this additional logic challenge as a new question.

please accept Momi's suggestion (ID: 37288103)..i hit mine by mistake

I'll create a new question for my challenge.