Search code examples
sqlcursordb2dynamic-sqlsql-pl

How to use dynamic SQL in FOR loop statement?

Is it possible to use dynamic SQL in a FOR statement?

I am using a FOR loop to handle a batch of records from a SELECT statement. However, what if I want the SELECT statement to be framed at run time, such as the table name may be stored in a variable.

for thisRecord as
    select myColumn from MyTable  --can this be dynamic?
do
    .......
end for;

I want something like the following

declare myCursor cursor for stmt;

set dynamicStmt = 'select myColumn from '||varTable;
prepare stmt from dynamicStmt;

for thisRecord as myCursor
do
    ......
end for;
Solution

  • You could simply use FETCH in a loop.