Skip to Content
0

Is the the proper way to compose a stored procedure?

Aug 03, 2017 at 09:09 PM

60

avatar image
Former Member

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


SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Lars Breddemann
Aug 04, 2017 at 12:27 AM
1

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

I appreciate your response; I utilized it in my SP, but I am getting an error on activation. It's saying,

"TABLE_NAME: symbol not found"

I understand that this is because the table I am counting does not currently exist. However, does this pose an issue with respect to running the stored procedure? I guess what I am asking is whether or not this should still work during the first go around. If not, should I simply create a table with 1 record for the first go around?

Regarding your statements towards the end, unfortunately it is necessary to create a table, as querying the CV itself takes nearly 130 gb on the server even after optimizations(was taking 200+ gb on the server). (Lots of hierarchy data, and about 35 accesses to tables of 153 million records or larger)

Persisting the data is the only choice, at the moment, and it helps that querying a persisted table gives just about instant response.

0

The SQL works in a standard HANA setup.

Can you share your code?

0