Skip to Content
0

Use Input Variable for column in HANA procedure

Dec 06, 2016 at 02:28 PM

135

avatar image

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

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

1 Answer

Best Answer
Florian Pfeffer
Dec 06, 2016 at 02:50 PM
0

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Thank you! It works..

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

Regards,

Philipp

0

Yes, using the same rules.

0