Skip to Content
avatar image
Former Member

IF EXISTS

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!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    May 15, 2012 at 04:15 PM

    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

    Add comment
    10|10000 characters needed characters exceeded