IF EXISTS..... DROP TABLE

[Former Member]

This might sound like a very stupid question, but I have been going through the HANA PDF's and cannot seem to find the answer I am looking for.

I am currently in the process of performing a HANA POC and am trying to create a large SQL script that will create multiple tables and populate those tables with some data.

Being OCD, I like my scripts to run through with zeros errors. The part where I need the help is in the DROP TABLE part of the script.

I have the DROP TABLE lines there, but if a table does not already exists, I get an error message.

Does HANA have the equaivilant of a IF EXISTS ..... DROP TABLE ??

Any help would be greatly appreciated!

[Ravi_Channe · Accepted solution]

I think you can make use of TABLES and search for existance of the table (column = table_name) to be dropped.

You can write a procedure and also make use of EXISTS (if required).

Regards,

Ravi

[Ravi_Channe · Accepted solution]

I think I jumped too early into this.

As you mentioned, you need completely ERROR free code which doesn't throw any exception (which I am not sure how to handle in HANA as of now, like specific "No data Found", "SQL Exception" etc).

To achieve this, I think you might have to write a custom program to delete the table with table_name and schema_name as input parameters and dynamic SQL with "EXEC 'DROP TABLE '||<input_table_name>; type command.

Not sure if I am over complicating this, but worth a try.

Regards,

Ravi

[Ravi_Channe · Accepted solution]

As I said, I AM overcomplicating the things.. but what the heck, I could try something new

=================================

drop procedure pr_drop_table;
CREATE PROCEDURE pr_drop_table (
IN  v_in_schema    VARCHAR  (30), -- Schema Name holding table to be dropped
    IN  v_in_table   VARCHAR  (30)    -- Table name to be dropped
)
LANGUAGE SQLSCRIPT
AS
  v_tab_exists smallint := 0;
BEGIN

-- Create a dummy table 
  CREATE TABLE t_dummy (v_tname VARCHAR(30));

-- Insert into the dummy table a value from TABLES with table_name as parameter

  INSERT INTO t_dummy
  SELECT table_name FROM TABLES
  WHERE schema_name = :v_in_schema AND table_name = :v_in_table;

-- Check how many records are inserted
-- If table exists, ROWCOUNT will be 1 or else 0

  v_tab_exists := ::ROWCOUNT;

  IF v_tab_exists > 0 THEN
    EXEC 'DROP TABLE '||:v_in_table;
  END IF;

-- Delete the dummy table

  DROP TABLE t_dummy;
END;

[rama_shankar3 · Accepted solution]

Ravi:

As usual, You Rock Man!   Cool..code....

Thanks for trying it so quickly.  I had it in my to-do list to try  later tonight...Now its gone...

Regards,

Rama

[Ravi_Channe · Accepted solution]

.. Thanks..

It was a nice break from my current SQL coding in HANA, where I am trying to re-write a BW transformation ABAP code, in HANA SQL stored procedure.

🙂

Regards,

Ravi

[hamed_dadras · Accepted solution]

This message was moderated.

[pm_witmond · Accepted solution]

Hi Ravindra,

Nice function, thanks

But the input should be converted to upper case and I don't know how to fix that in the procedure.

Thanks,

Paul

[rama_shankar3]

I have not use it myself but your SQL should be something like this:

IF EXISTS

(SELECT TABLE_NAME FROM SYS_sometabe WHERE field_NAME='<table_name>') DROP TABLE <table_name>;

Regards,

Rama

[Former Member]

Hi Guys....

Thank you so much for your comments.

I will play around with your suggesstions and report back.

Anton

[Ravi_Channe]

Hi Anton,

Did the suggestions help ? Did you find any better option for the requirement ?

Regards,

Ravi

[Former Member]

Hi Ravindra...

It seems that your process using the proc is teh best solution...

I posted the same question in a couple of places, and you proc solution seems to be/work the best.

Thank you for that!!!

A

[Former Member]

I have a similar question.

I would like to do something like this:

IF EXISTS (SELECT ID FROM  <SOME TABLE> WHERE ID=1) THEN

DELETE FROM <SOME TABLE> WHERE ID=1;

END IF;

my <SOME TABLE> only exists in 1 schema but not the other, and I'd like to have the above statement in a stored proc that would be shared by both schema.

however it returns an error saying that

Could not execute 'IF EXISTS ...

SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "IF": line 1 col 1 (at pos 1)

Any idea?

Thanks a lot!