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

Dynamic object name has a lot of disadvantages.

• It violates all the security, that SAP BW has.
• It does not keep track of object references, so you cannot get where-used list for your ADSO in ABAP or in HANA (via OBJECT_DEPENDENCIES view).
• It has no flexibility in terms of object replacement: imagine, in some day you'll need to use Composite Provider or infoobject master data table to look in. In HANA side you cannot easily get the base tables for that. Also all the fields you will use should have the same name across all the objects or should be passed as parameters.

But there are some options.

1. Create Composite Provider, where you'll union all the objects you need to access, map customer name and location to output, generate external HANA view for this object and use it in your select with restriction on INFOPROV dimension, instead of dynamic object name. Or not so good, but working way - use 0BW:BIA:<infoprovname> column view generated by default in HANA to acces that same data.
2. A variation on composite provider, but from ABAP side: create CDS view, that wraps all that base tables into single UNION with object identifier column, coded in SELECT list for corresponding object.
3. Use RSDRI_INFOPROV_READ function module to read that data from the desired infoprovider (dynamic name passing) in ABAP, then pass that data to AMDP.
4. Worst case as for me, but works only in HANA 2.0, because in 1.0 there's no INTO addition in EXEC or EXECUTE IMMEDIATE. Generate dynamic SQL, use EXECUTE IMMEDIATE statement in SQLScript with INTO addition, grab the content of its output to three variables (for customer, location and date) and then fill the tabular result with select ... from dummy:

execute immediate 'select customer, location, min(date)
from <dyn_tab_name>
where customer = ''' || :p_cust || '''
  and location = ''' || :p_loc || '''
group by customer, location'
  into lv_cust, lv_loc, lv_date;

outTab =
  select :lv_cust as customer
    :lv_loc as location,
    :lv_date as min_date
  from dummy;

I have no HANA 2.0 DBMS to check how to use bind variables with USING for parameter passing to EXEC instead of plain concatenation, and documentation have no examples of it. Hope Lars will add some info about that functionality.

And one more point on that dynamic code: if you can, pass multiple customers and locations to retrieve, because for single-row processing in HANA parsing can take almost the same (or maybe multiple times more) time that data retrieval process. So select from unioned data for multiple customers will perform better:

Answer 2

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.