Error while creating function in SAP HANA

[Former Member]

CREATE FUNCTION fnMultiRows

( @ReturnRows int  )

RETURNS

@Table_Var TABLE ( C1 INT )

AS

BEGIN

  DECLARE @i int

  set @i = 1

  WHILE @i <= @ReturnRows

  BEGIN

  insert into @Table_Var VALUES (@i)

  set @i = @i + 1

  END

  RETURN

END

[Former Member]

Hi Christian,

Thanks for your valuable reply.I had go through your link but could not get much idea regarding that.Can you tell me what is the alternative for Declaring variables in SAP Business One Version for HANA in HANA studio.

Thanks,

Jaydeep

[christianlechne]

Hi Jaydeep,

I am not quite sure if I got your question correctly, so let me rephrase it:

You want to create a stored procedure (SQL script procedure) in SAP HANA, right?

If yes, you want to know how to declare a scalar variable in that SQL script procedure?

The answer is given in the help.sap.com I have referenced -> Imperative SQL Script Logic -> Local Scalar Variables.

If my assumptions are not correct, please give me some hints, what is the issue.

BR

Christian

[Former Member]

Hi Christian,

I want to convert SQL Query to SAP HANA query and at that time I am facing problem as syntax error near @ as I am trying to Declare Variable. So I just want conversion for SQL Query to SAP HANA query which I mention in the start.Hope you get what I am looking for.

I am working on SAP B1 Version powered by SAP HANA and I want to create function in HANA studio.

[christianlechne]

Hi,

you want to transfer a stored procedure into a stored procedure that executabel in HANA. As already described you can do that ABAP managed (e. g. AMDP) or directly on HANA (SQL script procedure).

In any case in order to implement that logic you have to implement it SQLscript. Neither openSQL nor ABAP CDS Views are capable to reflect that logic. All guides are referenced above including the reason why a declaration of a local variable is not possible with an @. The syntax is (taken from the help referenced above)

CREATE PROCEDURE proc (OUT z INT)

LANGUAGE SQLSCRIPT READS SQL DATA

AS

BEGIN

DECLARE a int;

DECLARE b int = 0;

DECLARE c int DEFAULT 0;

  t = select * from baseTable ;

  select count(*) into a from :t;

  b = :a + 1;

  z = 😛 + :c;

end;

BR

Christian

[Former Member]

Hello Christian,

thanks for your quick reply as I go through your link and syntax. But when I am trying to create function as follows:

CREATE FUNCTION fnMultiRows (IN ReturnRows int, OUT Table_Var TABLE (NO INT))

LANGUAGE SQLSCRIPT READS SQL DATA

AS

BEGIN

    DECLARE i int

    set i := 1

    WHILE i <= ReturnRows

    BEGIN

        insert into Table_Var VALUES

        set i := i + 1

    END

    RETURN

END

I am still getting following error when I am creating function :

Could not execute 'CREATE FUNCTION fnMultiRows (IN ReturnRows int, OUT Table_Var TABLE (NO INT)) LANGUAGE SQLSCRIPT ...'

SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "SQLSCRIPT": line 2 col 10 (at pos 90)

So can you help me in solving this issue.If yes than it will be great helpful for me.

Thanks,

Jaydeep

[former_member320161]

Hi Jaydeep

Can you solve the problem???

I have a similar problem.

[christianlechne]

Hi,

please make yourself familiar with the SQL Script that is used in HANA in order to create a stored procedure that is compliant with SAP HANA:

SAP HANA SQLScript Reference - SAP Library

If you want to call the function from ABAP you can use either a CDS Table Function () or an AMDP (ABAP-Managed Database Procedures (AMDP) - About the ABAP for SAP HANA Development User Guide - SAP L...)

BR

Christian