Skip to Content
avatar image
Former Member

Use Input Variable for column in HANA procedure

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Dec 06, 2016 at 02:50 PM

    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

    Add comment
    10|10000 characters needed characters exceeded