Executing Dynamic SQL from a Parameterized View in SAP Datasphere

[MayuraNalawade]

Hello SAP Community,

I am working with SAP Datasphere and facing challenges executing dynamic SQL statements that are generated and stored in a parameterized view. I wanted to share my problem in detail and clarify the limitations I encountered, in case it helps others.

Scenario

I have a parameterized view DQ_RULE_GENERATED_VW that generates SQL statements dynamically based on some input parameters.

The view returns a column called GENERATED_SQL along with other metadata.

My goal is to execute these SQL statements dynamically within SAP Datasphere and capture the results in a table for downstream processing.

Challenges

1. Script Operators in Data Flows cannot execute arbitrary SQL:

• Datasphere Python Script Operators only process DataFrames from upstream sources.
• You cannot call a parameterized view directly or run SQL dynamically using sql.execute_query inside a Script Operator.

2. Parameterized views cannot be used as sources:

• Script Operators and many Datasphere operators only accept materialized tables or non-parameterized views.

3. No direct write access to space-owned tables:

• Even if I fetch results externally (e.g., with a Python script using a HANA connection), I cannot write the results back into Datasphere UI due to lack of privileges on space-owned tables.
• 
• I tried writing a stored procedure that executes the SQL statements dynamically, but it cannot write the results into the target table for the same reason. It seems DML operations can only be performed inside the Datasphere UI, not externally.
• 

4. External execution works but is not automated:

• I was able to fetch the GENERATED_SQL column using an external Python script, execute each statement dynamically, and get the results.
• However, I cannot send the results back into Datasphere without write access.
• Exporting results as CSV and manually uploading them is possible, but I would like a fully automated solution without manual steps.

Request for Community Input

I would like to know:

1. Is there a fully supported way in Datasphere to execute SQL dynamically from a parameterized view?
2. Are there recommended best practices for dynamically executing rules generated from parameterized views in Datasphere, especially when you don’t have write access to space-owned tables?

Any guidance or insights would be greatly appreciated!

Thanks,
Mayura

[MayuraNalawade]

Challenges Script Operators in Dataflows Script Operators in Dataflows cannot execute arbitrary SQL. Python Script Operators in Dataflows only process DataFrames from upstream sources. Parameterized views cannot be called directly, nor can SQL statements be executed dynamically via sql.execute_query inside a Script Operator. Script Operators accept only Materialized tables, or Non-parameterized views. Direct use of a parameterized view as a source in Dataflows is not supported. External Python Script Even when SQL execution is performed externally (e.g., via Python scripts connected to HANA), results cannot be written back into Datasphere UI due to privilege restrictions. The GENERATED_SQL column can be fetched and executed externally using Python scripts.But the technical user has only SELECT privileges, cannot perform INSERT/UPDATE/DELETE on space-owned tables. Limitations: Results cannot be returned automatically to Datasphere without write access. Exporting results as CSV and manually uploading them is possible, but this is not automated. Stored Procedure in HANA DB The stored procedure is created to execute SQL statements dynamically and write results to the table residing in FSDM_DEMO space. When executed as user DWCDBUSER#MAYURANALAWADE, it fails with insufficient privileges error. Database User Groups: Created DUGs, but the group schema does not have read/write access to DQ_RESULTS which resides in FSDM_DEMO schema. SQL View in SAP Datasphere SAP Datasphere / HANA does not allow a SQL view to execute dynamic SQL stored in a column. A SQL view is static: it can select, join, filter, aggregate, etc., but it cannot run SQL statements stored as text in a column. There is no “execute this column as SQL” feature inside a view. Table Function in SAP Datasphere Table Functions cannot execute dynamic SQL. There is no replacement for EXECUTE IMMEDIATE in Table Functions.