Stored procedures for data maintenance in HANA Studio

[john_hawk]

For a proof of concept, we are loading (and reloading!) data into table from local files. Data Services is NOT connect to our HANA box. To accelerate the process, it would be helpful to save the "DELETE FROM" or "TRUNCATE TABLE" into stored procedures. However, the stored procedures created under Content do not support INSERT/UPDATE/DELETE. Of course, I can always store the SQL as text files on my local PC, but it would be useful to store them centrally, where others could use them.

Any suggestions?

[Former Member · Accepted solution]

Hi John,

If you are creating the procedure from the Quick Launch option, it always creates procedures as read-only and hence INSERT/UPDATE/DELETE wouldn't be allowed. However you can create procedures in the sql editor without the Read-only option. For e.g.:

create procedure <proc_name>()

language sqlscript sql security definer

begin

delete from <table_name>;

end;

You are allowed to use "delete from" in procedures such as above. Truncate statement is allowed only with temporary tables within a procedure though.

Thanks,

Anooj

[john_hawk · Accepted solution]

Would the procedures created through the SQL editor be visible through HANA Studio?

I'm trying to create objects which could be reused by others.

Thanks,

John Hawk

[rama_shankar3 · Accepted solution]

Yes procedures created in sql editor will be visible provided security is setup correctly.

Regards,

Rama

[Former Member · Accepted solution]

Yes, it gets created under the schema of the user that the sql editor is connected to. Alternatively, you can create it under a schema of your choice in which case you need to name your procedure as <schema>.<proc_name> and your user should have "create any" access on that schema.

Once the procedure is created, you can give execute access to it under "sql privieleges"to any other user by:

a. Giving "execute" access to the whole schema

or

b. Giving "execute" access to just the procedure

Thanks,

Anooj

[john_hawk · Accepted solution]

Where in HANA studio  would they be visible? In the Content folder, under the defined schema, in the Procedures subfolder?

Thanks,

John Hawk

[Former Member · Accepted solution]

In the catalog under the schema and procedure sub-folder.