Skip to Content
avatar image
Former Member

Is the the proper way to compose a stored procedure?

Basically, what I would like to do is check if a table exists -- if it does, drop that table and create a new table with the same table name, otherwise, simply create the new table. This is my assumption as to how it should be done, but any help on the matter would be much appreciated.

IF EXISTS (TABLE_NAME)
    BEGIN
        DROP TABLE TABLE_NAME;
        CREATE COLUMN TABLE TABLE_NAME
        AS
        (
            SELECT *
            FROM CALCULATION_VIEW
        )
    END
ELSE
    BEGIN
        CREATE COLUMN TABLE TABLE_NAME
        AS
        (
            SELECT *
            FROM CALCULATION_VIEW
        )
    END


Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Aug 04, 2017 at 12:27 AM

    "IF EXISTS" is neither a standard SQL not a HANA SQL Script command.

    It's something found in T-SQL (MS SQL Server) and I guess you're trying to port code to HANA here.

    To achieve the same result, you have to run a query against the DB catalog, e.g.

    SELECT count(*) as table_cntFROM TABLES WHERE TABLE_NAME ='<your table name>' and SCHEMA_NAME = current_schema;

    After that, you simply check if the table_cnt is larger than zero.

    Having said all that: the approach of storing results from complex calculation views is typically not a great choice.

    One major misunderstanding is that a SELECT * FROM CALCVIEW would result in a table that could work as a stand in for queries against the calc view. That is very often not the case - especially when dealing with different levels of aggregation.

    Add comment
    10|10000 characters needed characters exceeded