on 09-02-2014 12:32 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
76 | |
9 | |
8 | |
7 | |
6 | |
5 | |
5 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.