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...@hotmail.comwrote:

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