How to use a field from an input parameter of a HANA stored procedure to generate table name dynamically for execute_immediate statement?

Question

I have been trying to solve a requirement with no luck where I have to pass a table containing 3 fields: object name, customer & location from an AMDP to a stored procedure. The stored procedure should be used to return a table that stores the same customer, location & a min(date).

The min(date) is found in a table which is derived from the object name as follows: the concatenation of '"/BIC/A' || (object name passed from AMDP) || 2"'. So, let's say if the AMDP passes the parameter object as YCUSTM01, then the table name to look for the minimum date should be "/BIC/AYCUSTM012". Thus I should be able to make an execute_immediate statement (maybe?) to find what is the minimum date for a customer on a specific location & return that table back to the AMDP for further calculations.

Input Table:

| Object_name | Customer | Location |
+-------------+----------+----------+
| YCUSTM01    | Walgreen | Chicago  |

Sample Data in "/BIC/AYCUSTM012":

| Customer | Location | Date       |
+----------+----------+------------+
| Walgreen | Chicago  | 24.09.2020 |
| Walgreen | Chicago  | 07.02.2019 |
| Walgreen | Chicago  | 12.12.2012 |
| Walgreen | Chicago  | 01.04.2015 |

Desired Output Table from stored procedure :

  select
    customer,
    location,
    min(calday)
  from "/BIC/AYCUSTM012"
  where customer = :customer
    and location = :location
  group by
    customer,
    location;

  | Customer | Location | Min_date   |
  +----------+----------+------------+
  | Walgreen | Chicago  | 12.12.2012 |

Any lead would be very much appreciated.

Answer 1

astentx is quite right: it's not the best idea to access SAP BW ADSO tables directly in your code. Instead, using the external views of those ADSO objects is recommended.

But this is not the main issue with the dynamic SQL approach. Again, astentx already provided a comprehensive overview of why it's not a good idea to use dynamic SQL, so I'll not repeat this again.

The solution approach I recommend takes a few more steps and leads to a separation of the code for data selection and data processing/query computation.

The complete approach including a demo is described here: The Lars Breddemann Blog: Separate business logic from tables and avoid dynamic SQL, so here the rough outline for it:

• create a table function that performs the computation you want on an appropriate table type instead of a specific table
• for each table you want to use the logic on, create boilerplate code that selects from the table and transforms it to match the table type of the table function
• now only the boilerplate code depends on the physical table (something you would have to have anyhow, as you would need to input the parameters for the table somewhere) and the computation/business logic is independent of any physical tables.