Question Re: IF EXISTS..... DROP TABLE in Technology Q&A

IF EXISTS..... DROP TABLE

Former Member — Thu, 10 May 2012 11:47:49 GMT

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!

Re: IF EXISTS..... DROP TABLE

Ravi_Channe — Thu, 10 May 2012 13:23:21 GMT

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

Re: IF EXISTS..... DROP TABLE

rama_shankar3 — Thu, 10 May 2012 13:41:25 GMT

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

Re: IF EXISTS..... DROP TABLE

Ravi_Channe — Thu, 10 May 2012 14:16:46 GMT

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

Re: IF EXISTS..... DROP TABLE

Ravi_Channe — Thu, 10 May 2012 14:28:42 GMT

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;

Re: IF EXISTS..... DROP TABLE

rama_shankar3 — Thu, 10 May 2012 14:48:52 GMT

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

Re: IF EXISTS..... DROP TABLE

Ravi_Channe — Thu, 10 May 2012 15:03:05 GMT

.. 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

Re: IF EXISTS..... DROP TABLE

Former Member — Fri, 11 May 2012 05:57:15 GMT

Hi Guys....

Thank you so much for your comments.

I will play around with your suggesstions and report back.

Anton

Re: IF EXISTS..... DROP TABLE

Ravi_Channe — Wed, 16 May 2012 05:51:11 GMT

Hi Anton,

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

Regards,

Ravi

Re: IF EXISTS..... DROP TABLE

Former Member — Thu, 17 May 2012 09:19:31 GMT

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!!!

Re: IF EXISTS..... DROP TABLE

Former Member — Mon, 04 Mar 2013 23:28:26 GMT

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!

Re: IF EXISTS..... DROP TABLE

hamed_dadras — Thu, 09 Apr 2015 11:28:44 GMT

This message was moderated.

Re: IF EXISTS..... DROP TABLE

pm_witmond — Wed, 09 Sep 2015 19:05:40 GMT

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