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]

Re: Dynamic sql statement called in a stored procedure SP

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_TES T
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.SYSDUMMY 1';--

PREPARE S_RETURN FROM V_RETURN;--

OPEN C_RETURN;--
END;


--Jeff