Dynamic sql statement called in a stored procedure SP

[Justin]

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;

[jefftyzzer]

On Oct 3, 8:38*am, Justin <[e-mail]:

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