Execute SQLScript Statements Dynamically from SAP HANA Database Table

SAP HANA database developer can execute SQL statements dynamically with Execute Immediate SQLScript command like shown in this tutorial read from M_SYSTEM_INFORMATION_STATEMENTS system monitoring database view.

SAP HANA database administrator or SQLScript developers can easily execute SQL statements dynamically by using SQLScript Execute Immediate command. This SQLScript tutorial shows how to execute SQL statements read from a system database table PUBLIC.M_SYSTEM_INFORMATION_STATEMENTS and stored into a string variable. Programmers can also find the sample SQL script in the tutorial.

Following SQLScript batch command shows how SQL programmers can execute SQL statements dynamically by reading from a HANA database table. For the sake of the SQLScript tutorial, I used a special SAP HANA database table names M_SYSTEM_INFORMATION_STATEMENTS which stores SQL statements for HANA database administrator to check system information or status.

execute SQLScript sql statements by reading HANA database table dynamically

"PUBLIC"."M_SYSTEM_INFORMATION_STATEMENTS" can be queried by every database user on SAP HANA database since it is created as a public synonym.
In the sample query, I filter table contents for Sessions information by using WHERE clause on "name" column. If SQL programmers display the contents of the M_SYSTEM_INFORMATION_STATEMENTS table, they will find many other useful SQL statements to monitor the HANA database status.

At the beginning of the SQLScript, a variable declaration takes place.
Following DECLARE SQL command, the field contents (SQL query) for Sessions is read and stored into the SQL variable immediately.
The last step in the SQL script enables developers to execute dynamic SQL statements with "EXECUTE IMMEDIATE" command.


do begin



declare sqlstatement varchar(9392);



select statement into sqlstatement

 from "PUBLIC"."M_SYSTEM_INFORMATION_STATEMENTS"

 where name = 'Sessions' ;



execute immediate :sqlstatement;



end;

Code

The output on a sample SAP HANA database displaying sessions information is seen in below screenshot.

execute dynamic SQL statements stored in HANA database table

Execute Immediate is a powerful SQLScript command for SAP HANA database developers. One single disadvantage of EXECUTE IMMEDIATE is it can lead to SQL injection. To prevent misuse of EXECUTE IMMEDIATE SQL command, the sql script string passed as an argument should be checked for statements like DROP, DELETE, etc before execution.