Skip to Content
0

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

Dec 13, 2017 at 12:26 PM

102

avatar image

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)

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

ERALPER YILMAZ Dec 13, 2017 at 04:33 PM
0

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;
Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi,

just assume I'm going to perform insert operation using dynamic sql statement

example:

 sqlstmnt = insert into <tbl_nm> select * from <tbl_nm> where "DATE" = :d;
EXECUTE IMMEDIATE sqlstmnt USING :DATE_1

but when I try this in hana procedure it throws an error near USING..

0

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

0