Skip to Content

EXEC and EXECUTE IMMEDIATE in HANA

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jun 20, 2016 at 03:59 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 21, 2016 at 03:41 PM

    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

    Add comment
    10|10000 characters needed characters exceeded