Skip to Content
0

Using Alter in a Stored Procedure

Jul 03, 2017 at 02:03 PM

86

avatar image
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).

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Florian Pfeffer
Jul 04, 2017 at 03:49 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded