How return dynamic SQL value in procedure

Question

I have a procedure that takes in parameters, and makes a dynamic statement based on those. The statement always returns a numeric value. How do I return that value, or pass it to the variable?

For example the following:

DECLARE VAR1_INPUT INT;
DECLARE SQLSTRING VARCHAR(5000);
DECLARE OUTPUTVAR INT;
SET VAR1_INPUT = 5;
SET SQLSTRING = 'select next value into :OUTPUTVAR for MYSCHEMA.MYSEQUENCE_'||CAST(VAR1_INPUT AS VARCHAR(25))||' from sysibm.sysdummy1';
EXECUTE IMMEDIATE SQLSTRING;

I suppose I could use a cursor but this is going to be executed a lot and needs to be lightweight, I'd rather avoid cursors. And don't get fixated on the nonsensical dynamic SQL above that's just an example. :)

Edit: The original question had selecting a value from a table as the question. The answer matched that, so I'm marking it as the correct answer. Later I realized sequences work differently so I edited the question. This part I never figured out.

Answer 1

Try

DECLARE VAR1_INPUT INT;
DECLARE SQLSTRING VARCHAR(5000);
DECLARE OUTPUTVAR INT;
SET VAR1_INPUT = 5;
SET SQLSTRING = 'select MYCOL INTO :OUTPUTVAR FROM MYSCHEMA.MYTABLE where MYVAL = '||CAST(VAR1_INPUT AS VARCHAR(25));
EXECUTE IMMEDIATE SQLSTRING;

SELECT INTO statement

PS. SELECT must return one row only. If not then limit the amount of rows returned (with some definite ordering).

• Kahn: Will test this soon, thank you! Yes it will only return 1 row.
• Kahn: Edited the original question. Apparently sequences don't work like that. I assumed the statement would be the same. Would you know how to do this with a sequence?