0

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.

0

1 Answer 1

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).

2
  • Will test this soon, thank you! Yes it will only return 1 row.
    – Kahn
    Commented Dec 21, 2021 at 12:32
  • 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?
    – Kahn
    Commented Dec 21, 2021 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.