Is there a function Equivalent of eval() of javascript or Ncalc of .net in SQLscript?

[Former Member]

Hi Guys,

I am storing my formula's in a table and accessing these formulas in SQL stored procedure, at this point I don't know the number of variables in the formula or computation model of the formula. I want to perform required operations specified in the formula by getting the variable values stored in other table .

I am able to achieve this using xsjs (javascript eval() function) but I wanna implement same thing using SQL script.

-> Is there any function that is equivalent to eval or Ncalc in SQL script??

-> is it possible to call XSJS code in stored procedure ??

-> this kind of an implementation is it possible in SQLscript??

Regards,

Vijay.

[Former Member · Accepted solution]

Hi,Vijaykumar

Use dynamic sql to "eval"

create procedure TEST.eval()
language sqlscript as
vart nvarchar(10);
begin
vart:='1+3-7';
create table #varc as (select 1 as q from dummy) with no data;
EXECUTE IMMEDIATE 'insert into #varc (select '||:vart||' as q from dummy)';
select * from #varc;
drop table #varc;
end

[Former Member · Accepted solution]

Hi Buslov,

I tried to create stored procedure ,

Initially I was getting a error to declare "vart" so I defined it as a

declare vart NVARCHAR;

but now it throws the following errors :

Could not execute 'EXECUTE IMMEDIATE 'insert into #varc (select '||:vart||' as q from dummy)'' in 28 ms 641 µs .

SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "EXECUTE": line 1 col 1 (at pos 1)

and

Could not execute 'call "_SYS_BIC"."ID.caaA.models/eval"' in 91 ms 218 µs .

SAP DBTech JDBC: [1301]: numeric or value error:  [1301] "_SYS_BIC"."ID.caaA.models/eval": line 7 col 2 (at pos 253): [1301] (range 3) numeric or value error exception

I am not familiar with Dynamic SQL, Could you please help me to resolve these errors, and also can you give me the link which explains Dynamic sql in sql script.

Regards,

Vijay.

[Former Member · Accepted solution]

Vijaykumar, my name - Dmitry;)

You have to use catalog procedure for dynamic SQL, not package(Read only)!

http://help.sap.com/hana/SAP_HANA_SQL_Script_Reference_en.pdf

page 69

[former_member182302 · Accepted solution]

Hi Dimitry,

Even with out creating any table ( or even temporary) we can still get the result right like below ?

create procedure eval()
language sqlscript as
vart nvarchar(10);
begin
vart:='1+3-7';
EXECUTE IMMEDIATE 'select '||:vart||' as q from dummy';
end

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

No, you need table to put result for select it after execution of dynamic sql

[former_member182302 · Accepted solution]

I didnt get you . Even without table and selecting it, I still get the same result with above code right? As we are using EXECUTE IMMEDIATE rather than EXEC?

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

Yes,right.

[Former Member · Accepted solution]

Hi Dmirty,

Thanks for Quick response,

Can we declare variable at run time lets say

declare 'variable||'+:i decimal(20,4) ;

where i varies depending on my query response

Regards,

Vijay.

[Former Member · Accepted solution]

I never heared about dynamic declare. May be it will be better to use ARRAY() for your case?