XS JS function

[Former Member]

All,

In SQL Server we are able to create vb.net or c#.net functions and leverage them through TSQL.

Do you know if is possible to create a XS JS function or similar and call it from SQL (SAP Hana)

All assistance appreciated. An example would be awesome.

Regards,

Will

[pfefferf]

Hello Will,

regarding your "... or similar". If you are not only focussed here on XSJS functions, you can have a look to the User Defined Functions you can define in SQLScript (User Defined Function - SAP HANA SQLScript Reference - SAP Library). These functions can be used in your SQL. Not so mighty regarding the features like in XSJS, but maybe something which brings you already a step forward.

Best Regards,

Florian

[Former Member]

All,

I'm trying to create a function similar to the following in Hana, now select statements are not allowed in Hana Scalar functions hence I was looking for another option. Something I can use inline in a query.

Thanks for all the comments so far.

Regards,

Will

**********************************************************************************************

CREATE FUNCTION [dbo].[GetCurrencyValue](@myString varchar(500), @validChars varchar(100))

RETURNS varchar(500) AS

BEGIN

While @myString like '%[^' + @validChars + ']%'

Select @myString = replace(@myString,substring(@myString,patindex('%[^' + @validChars + ']%',@myString),1),'')

Return @myString

END

**********************************************************************************************

[former_member185132]

If you have XSJS as part of your solution, you could do this with a simple regex replace.

The below example shows how to do this:

var allowed = "A-Za-z";     // Only letters allowed
var rx = new RegExp("[^" + allowed + "]","gi");

var str = 'HelloWorld1234!@#$%^';
var newstr = str.replace(rx, ' ');
console.log(newstr);  // returns "HelloWorld "

(Reference for more details)

[SergioG_TX]

you can only call SQL stored procs from XS and also you can call procs from odata and if using XS exit functions you would go from the odata into the XSJS but i dont think you can do what you mentinoed there.

[thomas_jung]

No you can can call "up" to the XS layer from stored procedures/SQL.