cancel
Showing results for 
Search instead for 
Did you mean: 

IF EXISTS

Former Member
0 Kudos

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!

Accepted Solutions (0)

Answers (1)

Answers (1)

sorin_radulescu
Employee
Employee
0 Kudos

Hi,

Exists function is working in HANA but in different way.

It can be used just into where clause.

For your problem I have a solution:

Create the follow store procedure into your system:

drop procedure existstable;
create procedure existstable
( IN tablename VARCHAR(20), IN schemaname varchar(20)
) LANGUAGE SQLSCRIPT AS myrowid integer;

BEGIN
myrowid := 0;
select count(*) into myrowid from "PUBLIC"."M_TABLES" where schema_name =:schemaname and table_name=:tablename;

IF (:myrowid > 0 ) then
exec 'DROP TABLE '||:schemaname||'.'||:tablename;

END IF;

End;

in your script before the creation of any table you have to call the procedure:

call existstable('name of table', 'name of schema where the table is')

in this way your script will not have errors.

If you need exist for schema or store procedures then you have to replace

the select statement with selection from P_SCHEMAS_ or P_PROCEDURES_

I hope this will help you.

Regards

Sorin Radulescu

SAP CSA- Customer Solution Adoption

hamed_dadras
Explorer
0 Kudos

This message was moderated.