CODEWITHSUNDEEP
c#hana
Ram Kishore
Ram Kishore

Reputation: 35

Warning is thrown while dynamically dropping a temporary table in SAP HANA

I am able to drop the temporary table dynamically by using 

EXEC 'DROP TABLE '||:schemaname||'.'||:tablename;

where schemaname and tablename are obtained as input in the procedure.

Problem: I am getting a warning stating

Not recommended feature: DDL statement is used in Dynamic SQL (current dynamic_sql_ddl_error_level = 1)

Due to this warning, I'm getting exception in C# when I use this procedure.

Isn't DROP supported in dynamic SQL? Help me to handle this either from SAP HANA or at least skip this warning from being caught as an exception in C#

Upvotes: 0

Views: 1131

Answers (1)

Lars Br.
Lars Br.

Reputation: 10396

Yes, it is supported, but you get a warning nevertheless as it's considered problematic to use DDL in dynamic SQL. Basically, you want to avoid doing schema changes in your SQL Script. For temp tables there's not really a need to drop them anyway.

The memory can easily be freed up via TRUNCATE TABLE if that's a concern.

Your C# program on the other hand should learn to handle and differentiate errors and warnings. The message you got here is a warning and does not mean an error.

If that's not possible for you to do, then you can still use the sledge-hammer approach to problems: switch off the warning by setting the indexserver parameter sqlscript - dynamic_sql_ddl_error_level = 0

That's of course not recommended, as you may miss out other warnings.

Upvotes: 1

Related Questions