sqlstored-procedureshanadynamicquery

Dynamic update query with parameters in SAP HANA procedures

I have a SAP HANA procedures to update a table. I'm trying to create a dynamic SQL query, this is my example:

declare _field varchar(100) := 'NAME';
declare _name varchar(100) := 'david';
declare _id integer := 1;
DECLARE SQL_STR VARCHAR(3000);

SQL_STR := 'UPDATE "_SYS_BIC"."TEST_TABLE" SET "'||_field||'" = '||_name||' WHERE "ID" = '||:_id;

EXECUTE IMMEDIATE (:SQL_STR);

But in the console I have this error:

Service exception: [260] invalid column name

How can I fix it?

Solution

  • I found the correct syntax to fix it

    declare _id integer := 1; 
declare _name varchar(100) := 'david';
declare _field varchar(100) := 'NAME';
DECLARE SQL_STR VARCHAR(3000);

SQL_STR := 'UPDATE "_SYS_BIC"."TEST_TABLE" SET "'||_field||'" = '''||_name||''' WHERE "ID" = '||_id;
EXECUTE IMMEDIATE (:SQL_STR);

    If I have to set a column or table name the right syntax is "'||_field||'"

    To set a string value the syntax is '''||_name||''' the first two apostrophes are used for the escape so we can say to the query that the value is a string, the last one apostrophe is used to concatenate the value to the rest of the query string. The three apostrophes must be written without spaces among them.