Stored Procedure truncate and Grant for Local temporary tables

[Former Member]

We are having issues truncating a table in a stored procedure.

The work around we were using in development was to create a local temporary table (which can be truncated in a stored Procedure) . As a developer our Id in our QA environment cannot be Granted "CREATE ANY" schema privilege . This privilege allows the creation of all kinds of objects , which will be an audit issue  .

  Is there a way to grant an Id the ability to only create a local temporary table? Is there a work around for truncating a table in a stored procedure? We would prefer to not fill up the logs by using SQL DELETE statements in the stored procedure .

[Former Member]

Hi Kris,

You dont need to create Local Temporary tables using "Create" ... Insted use the HANA Tables Concept like below.

Table A = Select * from "SCHEM"."TABLE_NAME"

In the above statement a new Table "A" is created at run time and drop off once the procedure is completed.

Usually we follow the same procedure when we want to store some temporary data ar run time of Stored procedure.

Regards

Jagan

[justin_molenaur2]

You can't perform and DDL type statements within a stored procedure, and TRUNCATE is also not allowed.

I was with a client who got around this by issuing a TRUNCATE statement within a script, and THEN calling the stored procedure. This cut off like 25% of processing time since the DELETE was not held in rollback.

Regards,

Justin