syntax error for dynamic column for update query

[Former Member]

Hi Experts,

Please check following query. i have incorporated it in my store procedure.

EXEC 'update TBL_M set '||:COL_NAME||' = '||:R."W_QTY"||'where WID ='||:R."W1";

i am getting following error.

Could not execute 'call USP_CW' in 131 ms 107 µs .

SAP DBTech JDBC: [257]: sql syntax error:--: line 29 col 5 (at pos 625): [257] (range 3) sql syntax error exception: sql syntax error: incorrect syntax near "W1": line 1 col 44 (at pos 44)

could you please let me know, where i am doing wrong.

[pfefferf · Accepted solution]

Hello Umesh,

in front of the where clause a blank is missing to separate the value set in the column from the word "where" (-> ... || ' where WID = ' || :R."W1";).

In case your column types are character based you should also enclose the values in single quotes.

Regards,

Florian

[Former Member · Accepted solution]

Hi Floriran,

Thanks for your reply. For learning purpose, i have created following SP.

create procedure USP_TEST()

as

begin

  declare var_name varchar(10) := 'UMESH';

  exec ' "select" '||:var_name||' "from dummy;" ';

end

I am able to compile it. As per suggestion, i have added " " quotes. but getting following error. Any idea?

Could not execute 'call USP_TEST' in 33 ms 72 µs .

SAP DBTech JDBC: [257]: sql syntax error: "UMESH"."USP_TEST": line 5 col 2 (at pos 84): [257] (range 3) sql syntax error exception: sql syntax error: incorrect syntax near """: line 1 col 1 (at pos 1)

[pfefferf · Accepted solution]

Hi,

you do not have to enclose the whole statement in double quotes. What I meant and described is for the case that your values set and compared against the columns are character based that they should be enclosed in single quotes.

For example if you compare the value 'abc' in a where clause against a character based column it should like '... where mycolumn = ''' || :lv_value || ''''.

Regards,

Florian

PS: As you are using dynamic SQL you should always keep in mind to check your inputs to avoid SQL injections.

[former_member182302 · Accepted solution]

Consider them as strings and try to concatenate them. In your last statement, the SQL you want to execute is :

SELECT 'UMESH' FROM DUMMY;

So while framing the dynamic sql, you need to frame that string like below:

  execute immediate 'select '''||:var_name||''' from dummy;';

We have totally 3 parts of the string and we need to have 'UMESH' ( with quotes) also as part of the string:

'select ' -- first part of the string

'' --  (to escape the quotes to get one quote )

:var_name -- concatenating the Variable

'' --  (to escape the quotes to get one quote )

' from dummy;' -- Last part of the string

So the procedure must be like this:

create procedure USP_TEST()
as
begin
  declare var_name varchar(10) := 'UMESH';
  execute immediate 'select '''||:var_name||''' from dummy;';
end

Hope this helps

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

Thank you Boss..... Its solved now.