on 05-10-2012 9:41 AM
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!
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.