0

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?

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Shiroga
  • 145
  • 2
  • 8
  • 20

1 Answers1

1

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.

Shiroga
  • 145
  • 2
  • 8
  • 20
  • 1
    While you've fixed the syntax error, be aware that the approach you're taking here is really "over"-flexible and a common sight in SQL code written by programmers used to work in other languages. Among the many disadvantages of this approach are: code has 'invisible' dependencies to your data model, the statements cannot be checked at compile time, there is the big change of SQL injection, SQL cache will get flooded by once-off statements... Instead of this, rather build specific procedures that work on specific entities in your data base. – Lars Br. Jul 20 '16 at 11:33
  • I know about the big problem of this approach, but for many reasons, in this case, I have to choose this way. Thanks alot for your help! – Shiroga Jul 20 '16 at 13:49