0

I have a request that includes a variable. But when executing the query, it does not display the result. I don't see the details of the error. What's wrong with the request?

DECLARE SQL_TEXT VARCHAR(3000);
DECLARE NumberInst VARCHAR(100);
SET NumberInst = '108';
SET SQL_TEXT = 'SELECT TOP 10 * FROM NRT_'||NumberInst||_'db_tmp.table_list';
CALL dbc.SysExecSQL(:SQL_TEXT);
Thom A
  • 88,727
  • 11
  • 45
  • 75
mister bull
  • 1
  • 1

1 Answers1

1

Legacy dbc.sysexecsql or execute [immediate] can't be used to return a result set. You should get an error message (maybe there's a condition handler suppressing it).

What you need is a result set cursor:

DECLARE NumberInst VARCHAR(100);
DECLARE SQL_TEXT VARCHAR(3000);

DECLARE cur1 CURSOR WITH RETURN ONLY FOR S1; -- result set cursor defined

SET SQL_TEXT = 'SELECT TOP 10 * FROM NRT_'||NumberInst||_'db_tmp.table_list';

PREPARE S1 FROM SQL_TEXT;
OPEN cur1; -- no result set returned when CLOSEd

Don't forget to define the SP using DYNAMIC RESULT SETS 1

dnoeth
  • 59,503
  • 4
  • 39
  • 56