cancel
Showing results for 
Search instead for 
Did you mean: 

How to drop a procedure, if already exists

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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

Answers (3)

Answers (3)

Former Member
0 Kudos

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

Former Member
0 Kudos

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

0 Kudos

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

Former Member
0 Kudos

Fernado,

Thank you for your answer. If I run the same code in SQL Console I get errors.

Only if I create another procedure that will look how to drop another procedure It runs correctly.

Then the problem is that I am in the loop how to drop the new procedure.

What I want to do is to execute a part of code that will drop a procedure directly on Hana without using another procedure. This will run directly from a SSIS package in a dataflow.


Regards,

Dimitris


Former Member
0 Kudos

Hi Dimitris,

I don't think DROP PROCEDURE is allowed in SQLScript.

Best regards,

Wenjun