Using Alter in a Stored Procedure

[Former Member]

I am trying to do a simple Stored Procedure which does a mass change of all HANA users to limit there memory consumption:

select user_name from users where creator like 'Z%';

FOR cur_row as mCursor DO
oname := cur_row.user_name;
ALTER USER cur_row.user_name SET PARAMETER STATEMENTMEMORYLIMIT = 15;
count := count + 1;
END FOR;

Why does it not work, and what is the work around you would suggest ? (please note that this is a work around since workload management is NOT suitable for our ODBC connections).

[pfefferf]

As many other DDL statement, also the ALTER TABLE statement is not supported in SQLScript (so far).

The way to use it in SQLScript, which is mentioned in the SQLScript reference, is to use Dynamic SQL. If you think about the usage of dynamic SQL please consider the "points to consider" to avoid e.g. SQL injections (for your example this is not issue, but maybe you use dynamic SQL for future requirements too).

Best Regards,
Florian