Skip to Content

issue while using EXECUTE IMMEDIATE with USING clause in a Procedure in SAP HANA

Hi Experts,

I'm trying to insert a record based on the date by using DYNAMIC SQL - EXECUTE IMMEDIATE in a procedure.

Here is my code:

Created Table:

Create table "KABIL_PRACTICE"."EXEC_IM_USING_TBL_1"
(
"S_DATE" DAte,
"ID" integer
);

insert into "KABIL_PRACTICE"."EXEC_IM_USING_TBL"  values ('2017-12-13',1);
insert into "KABIL_PRACTICE"."EXEC_IM_USING_TBL"  values ('2017-12-13',2);
insert into "KABIL_PRACTICE"."EXEC_IM_USING_TBL"  values ('2017-12-12',3);
TO Create a Procedure:
CREATE PROCEDURE "KABIL_PRACTICE"."DEMO360_EXEC_USING" AS
BEGIN
declare s varchar(10000);
declare rdate date;
select "S_DATE" into rdate from "KABIL_PRACTICE"."EXEC_IM_USING_TBL" where "ID" = 1;
 
   s := 'insert into "KABIL_PRACTICE"."EXEC_IM_USING_TBL_1" select * from "KABIL_PRACTICE"."EXEC_IM_USING_TBL"';
   EXECUTE IMMEDIATE  s  using :rdate;

end;

it throws an error like this:

Could not execute 'CREATE PROCEDURE "KABIL_PRACTICE"."DEMO360_EXEC_USING" AS BEGIN declare s varchar(10000); declare ...'
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "using": line 8 col 26 (at pos 343)

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Dec 13, 2017 at 04:33 PM

    Hello Kabilarasan,

    You don't need USING in following statement

    EXECUTE IMMEDIATE  s  using :rdate;

    Because your SQL string is not a parameterized string, you can directly use

    EXECUTE IMMEDIATE  s;
    Add comment
    10|10000 characters needed characters exceeded

    • Hi, the syntax error you got related with INTO or USING is just because of the HANA database version you are using. On a HANA db with "hana1sp12" I experience the same error, but same SQL code works without a problem on "hana2sp02" . Try to upgrade your systems to HANA 2.0 as quick as possible to use new enhancements like this case, hiearchy functions, etc.

      I've created a sample and used Execute Immediate command with parameters for input values and output values, it works successfully on SAP HANA 2.0