Skip to Content
avatar image
Former Member

How to drop a procedure, if already exists

Hi,

I want to run an sql script in Hana Studio that will drop a procedure if this procedure already exists.

Otherwise nothing will happen.

Thank you in advance.

D.E.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    avatar image
    Former Member
    Sep 02, 2014 at 02:07 PM

    Hi,

    You can just run "DROP PROCEDURE <PROC_NAME>". If the procedure exists, you will drop it; Otherwise, there will be an error saying something like "invalid procedure name" which means you do not have this procedure.

    DROP PROCEDURE - SAP HANA SQLScript Reference - SAP Library

    Best regards,

    Wenjun

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 02, 2014 at 03:01 PM

    You can use the view SYS.PROCEDURES to check if your procedure exists, something similar to this:

    declare v_proc_exists integer;

    select count(PROCEDURE_NAME) into v_proc_exists from SYS.PROCEDURES

    where PROCEDURE_NAME = '<PROC_NAME>';

    if :v_proc_exists > 0 then

      DROP PROCEDURE "<PROC_NAME>";

    end if;

    Cheers,

    Fernando

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 03, 2014 at 08:15 AM

    My idea is to do something like :

    SET 'MY_PROC' = 'createtable';

    SELECT count(*) as found FROM "PUBLIC"."PROCEDURES" WHERE "PROCEDURE_NAME" = (SELECT SESSION_CONTEXT('MY_PROC') FROM DUMMY);

    UNSET 'MY_PROC';

    IF (:found > 0) then

    DROP PROCEDURE "<PROC_NAME>";

    end if;

    But If statement does not run in SQL Console

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 03, 2014 at 08:52 AM

    Hi Dimitris,

    You can delete procedures that are created via SQL Editor, but be sure about dependencies between procedures. Also, it is not advised to delete procedures from catalog that were created via Repository (.hdbprocedures).

    Regards,
    TG

    Add comment
    10|10000 characters needed characters exceeded