IF EXISTS would exist

A recurring question of folks using SAP HANA is how to only run a DROP command for objects that actually exist in the database. One could argue that the effect of executing DROP does not depend on whether the object to be dropped exists, because after running the command it will not be there either way, but there is a bit more to it.

See, DROP commands, like say DROP TABLE, report back an error message when the object cannot be found (e.g. because it does not exist).

drop table bla;

Could not execute 'drop table bla'
SAP DBTech JDBC: [259]: invalid table name: BLA: line 1 col 12 (at pos 11)

This can be annoying when running scripts to set up a database structure which is a common part of product installation and update procedures.

On MS SQL Server there exists (ha ha ha – extra point for the flattest pun) an extension to the DROP command (and to other DDL commands): DROP IF EXISTS

SAP HANA up to now (HANA 2 SPS 03) does not provide such a functionality, but it is easy to come up with a workaround.

The following utility function can be used:

drop function obj_exists ;

create function obj_exists (in schema_name NVARCHAR(256)
                          , in object_name NVARCHAR(256))
returns obj_exists int
as 
begin
declare _exists int := 0;    
    
      select case 
                when count(*) >0 then 1
                else 0
             end into _exists
       from objects
       where 
           schema_name = IFNULL (:schema_name, current_schema)
       and object_name = IFNULL (:object_name, '');

    obj_exists = :_exists;

end;

The function returns 1 if the object exists and 0 if it does not. If the input parameters contain NULL, then the function still works using the CURRENT schema but will likely return 0 as output.

select 
    obj_exists (schema_name => NULL 
              , object_name => 'OBJ_EXISTS') as OBJ_EXISTS
from dummy;

OBJ_EXISTS
1         

The above worked because the OBJ_EXISTS function was created in the current schema. With that one can easily write a SQL Script that “looks before it fires“.