How to use schema-mapping in a stored procedure when you have more than one schema?

Question

I have a stored procedure on my HANA database where I need to join two tables from different schemas. These schemas are named differently in the development, staging and production system.

The obvious solution in this situation would be to use Schema-Mapping. But unfortunately schema-mapping only seems to work for the default schema of a stored procedure. When trying to reference an authoring schema in a stored procedure (ex. JOIN "AUTHORING_SCHEMA"."SOME_TABLE" ON ...) you get the error message "invalid schema name". So it seems like I can only use schema-mapping for one of the tables but not for both.

I know I can read the schema mappings in my stored procedure by querying the table "_SYS_BI"."M_SCHEMA_MAPPING", but I can't find out how to query from a schema when I have the schema name in a variable.

Answer 1

I would give it a try to work around this limitation by defining two synonyms using .hdbsynonym

For details on how to create design time synonyms using .hdbsynonym check https://help.sap.com/saphelp_hanaplatform/helpdata/en/52/78b5979128444cb6fffe0f8c2bf1e3/content.htm and https://help.sap.com/saphelp_hanaplatform/helpdata/en/4c/94a9b68b434d26af6d878e5f51b2aa/content.htm

There you can also find a description on how schema mapping works with hdbsynonym.

For details on synonyms in general see https://blogs.sap.com/2016/12/05/using-synonyms-in-sap-hana/

Answer 2

I solved this with a workaround which I am not entirely happy about, but which works for now.

I created a second stored procedure with the second schema as default schema. This procedure does nothing but SELECTing the contents of the second database table.

The first stored procedure calls the second one to load the data into a local table variable and then performs a JOIN between the first database table and the table variable.

This works reasonably well because the second table is rather small (16 rows at the moment, unlikely to grow beyond 100). But I wouldn't want to do it with a larger table.