When trying to assign a query result to local variables within a PROCEDURE or FUNCTION using Dynamic SQL only a DYNAMIC CURSOR (see below) can be used which leads to more complicated and lengthy code. However, using SELECT INTO (see below) could be used to accomplish the same value to local variable assignment in fewer steps. Our request is to allow the use of SELECT INTO with Dynamic SQL for local variable assignment as is allowed in other RDBMSs.
|Who would benefit from this IDEA?||As a customer, I want to be able to use SELECT INTO and Dynamic SQL to assign query result values to Local Variables to improve the readability and supportability of my code.|
How should it work?
In the example below, I show how we currently use Dynamic SQL with a CURSOR (what we call a DYNAMIC CURSOR) to assign a query result to a Local Variable and how we would like it to be accomplished via SELECT INTO with Dynamic SQL as well as the error thrown when using the SELECT INTO approach.
SET v_str_sql_cursor = 'SELECT ' || v_process_database_name || '.SEQ_DATAPIPELINE_PROCESS_H0.NEXTVAL FROM ' || v_process_database_name || '.DUMMY;';
OPEN c_table_cursor USING v_process_database_name;
FETCH c_table_cursor INTO v_stored_procedure_execution_id;
SET v_str_sql = 'SELECT ' || v_process_database_name || '.SEQ_DATAPIPELINE_PROCESS_H0.NEXTVAL INTO :v_stored_procedure_execution_id FROM ' || v_process_database_name || '.DUMMY;';
SQL Error : The statement was not processed because the statement contains an invalid use of one of the following: an untyped parameter marker, the DEFAULT keyword, or a null value.. SQLCODE=-418, SQLSTATE=42610, DRIVER=4.26.14
|Priority Justification||As a customer, I want to be create SOLID code and we believe that is best done by using SELECT INTO with Dynamic SQL to assign values from a query result to Local Variables with a FUNCTION/PROCEDURE.|
|Customer Name||O'Reilly Auto Parts|