EXEC and EXECUTE IMMEDIATE in HANA

[former_member39602]

Hi Experts,

I am trying to insert some data into table using exec or execute immediate in procedures, though procedure is created, data is not getting inserted into the desired table.

Also i want to capture the return code after the statement executed. Please help me out.

Below is the code which i am trying.

create procedure "ABC"."EFG"

AS

BEGIN 

     declare str varchar(500);

     declare v_stmt varchar(10);

     v_matnr := '123456';

     str := 'insert into "SCHE"."ZTEST_TABLE" values (' ||V_MATNR|| ')';

     v_stmt := EXEC (:STR);

END; 

Thanks,

Ebinezar

[pfefferf]

Hello Ebinezar,

there are several issues in your procedure which avoids the creation of your procedure:

• v_matnr has to be declared by the "declare" statement
• in case the v_matnr is a string (which it is, according to your assignment), then in your insert statement the value has to be enclosed in single quotes
• the EXEC function does not return anything you can collect in v_stmt; v_stmt has to be removed

Lower/Upper cases for variables do not matter.

Regards,

Florian

[SergioG_TX]

hi Ebinezar,

did you try the variable name with the same case.,.. since you declared it with small letters, then you should use it with small letters .. v_matnr  in both declaration and also in the insert dynamic statement