Dynamic sql statement called in a stored procedure SP

We have a stored procedure that dynamically builds a sql statement.

Is there an example of how to get the SP to return the result set of a
dynamically generated sql statement.

here is an oversimplified sql statement - just to prove the point:

set SQLStatement = 'select c1, c2 from syscat.tables ';
set SQLStatement = SQLStatement || 'where other stuff...'
set SQLStatement = SQLStatement || 'UNION select A2, B3 from X where
other stuff...'


--execute immediate SQLStatement;--
PREPARE stmt1 from SQLStatement;
EXECUTE stmt1;

On Oct 3, 8:38 am, Justin <kfw....RemoveThis@hotmail.com> wrote:
> We have a stored procedure that dynamically builds a sql statement.
>
> Is there an example of how to get the SP to return the result set of a
> dynamically generated sql statement.
>
> here is an oversimplified sql statement - just to prove the point:
>
> set SQLStatement = 'select c1, c2 from syscat.tables ';
> set SQLStatement = SQLStatement ||  'where other stuff...'
> set SQLStatement = SQLStatement ||  'UNION select A2, B3 from X where
> other stuff...'
>
> --execute immediate SQLStatement;--
> PREPARE stmt1 from SQLStatement;
> EXECUTE stmt1;

CREATE PROCEDURE RET_TEST()
LANGUAGE SQL
SPECIFIC ARCHIVE_RET_TEST
INHERIT SPECIAL REGISTERS
DYNAMIC RESULT SETS 1
BEGIN
DECLARE V_RETURN CHAR(30);--

DECLARE C_RETURN CURSOR WITH RETURN FOR S_RETURN;--

-- Build statement here
SET V_RETURN = 'SELECT * FROM SYSIBM.SYSDUMMY1';--

PREPARE S_RETURN FROM V_RETURN;--

OPEN C_RETURN;--
END;


--Jeff