Use Input Variable for column in HANA procedure

[Former Member]

Hello,

i need help by using input variables within an update statement in a HANA procedure.

First try:

Create procedure "PMETZGER"."NO_PCD_PR_MANIPULATION" (in country nvarchar(10), in lyear nvarchar(4)) 

language SQLSCRIPT as

begin

Update "PMETZGER"."NO_TAB_AQ_DEMAND" set :lyear = :lyear +1 where "Market" = :country;

end;

my second try:

Create procedure "PMETZGER"."NO_PCD_PR_MANIPULATION" (in country nvarchar(10), in lyear nvarchar(4)) 

language SQLSCRIPT as

v_select nVARCHAR(100) := '';

begin

v_select := 'Update "PMETZGER"."NO_TAB_AQ_DEMAND" set ":lyear" = ":lyear" +1';

v_select := :v_select ||' where '||' "Market" '||'=:country';

EXEC :v_select;

end; 

Call "PMETZGER"."NO_PCD_PR_MANIPULATION" ('Germany', 2017);

Could not execute 'Call "PMETZGER"."NO_PCD_PR_MANIPULATION" ('Germany', '2017')' in 15 ms 65 µs . SAP DBTech JDBC: [467]: cannot use parameter variable: "PMETZGER"."NO_PCD_PR_MANIPULATION": line 42 col 1 (at pos 1350): [467] (range 3) cannot use parameter variable exception: cannot use parameter variable: COUNTRY: line 1 col 82 (at pos 81)

I hope that somebody can help me.

Thanks

Philipp

[pfefferf · Accepted solution]

Hello Philipp,

your first try fails because in SQLScript you cannot define column names in a dynamic way except using dynamic SQL. Your second try fails because you assume, that the variable names (e.g. :lyear) are replaced with the actual values when the dynamic SQL is executed. But because you added the names to the statement string, they are just interpreted as string and not replaced.

I assume, that you wanna increase the value of a "year column" by the value 1 with the update. In that case you have to setup your dynamic SQL statement in following way:

v_select := 'UPDATE "PMETZGER"."NO_TAB_AQ_DEMAND"'
            || ' set "' || :lyear || '" = "' || :lyear || '" + 1 ' 
            || ' where "Market" = ''' || :country || '''';

If available on your HANA 1.0 patch level, please also use the SQL Injection Prevention Functions to avoid SQL injections.

Regards,
Florian

[Former Member · Accepted solution]

Thank you! It works..

Can i also get an input parameter for the "updating value" (instead of the fix value +1) ?

Regards,

Philipp

[pfefferf · Accepted solution]

Yes, using the same rules.