How to call a procedure in HANA

[Former Member]

I created a very simple procedure under my Schema. The procedure that selects one field from my HANA analytical view and sends it to the the output (which is a table with one field in it). The procedure saved and activated without issues.  I am now trying to call my procedure from SQL editor and keep getting syntax errors around the call statement and the parameters passed.

Here's what I tried:

I defined my procedure as "NAME_SEARCH" and output as a table VAR_OUT with one field in it 'NAME'. There is no input.

/********* Begin Procedure Script ************/

BEGIN,

VAR_OUT"= SELECT "NAME"

FROM "_SYS_BIC"."My-Schema/MY_ANALYTICAL_VIEW"

WHERE ("NAME" IN ('JOE','JIM')) GROUP BY "NAME";

END;

/********* End Procedure Script ************/

Once I save and activate it, the runtime version of the procedure appeared under _SYS_BIC , under folder "Procedure" as My-Schema/NAME_SEARCH

Next -  I opened the SQL editor and entered-

CALL "_SYS_BIC"."My-Schema/NAME_SEARCH";

and received the following error " Wrong number or types paramters in call: My-Schema/NAME_SEARCH : Line 1, column 17; (at pos16).

I tried:

CALL "_SYS_BIC"."My-Schema/NAME_SEARCH" (VAR_OUT)

I receive "Identifier mustbe declared"

I try to declare it as the table type that is generated in _SYS_BIC

CALL "_SYS_BIC"."My-Schema/NAME_SEARCH" (VAR_OUT) OUT "_SYS_BIC"."my-schema/NAME_SEARCH/tabletype/VAR_OUT")

and i received "Syntax error near "OUT" statement"

Any help will be appreciated.

Thanks!

[Former Member · Accepted solution]

Hi,

The following will work:

CALL "_SYS_BIC"."My-Schema/NAME_SEARCH"(?)

CALL "_SYS_BIC"."My-Schema/NAME_SEARCH"(NULL)

or

CALL "_SYS_BIC"."My-Schema/NAME_SEARCH"(?) with overview

'with overview' will show you the variable and the temporary table that stores the data.

Let us know if this helps..

Regards, Rahul

[Former Member · Accepted solution]

Thank you Rahul! That worked. So, now say I want to acctually pass values (which is my original requirement). I

I modified the code to accept a scalar variable  input "varname" as shown below.

/********* Begin Procedure Script ************/

BEGIN,

VAR_OUT"= SELECT "NAME"

FROM "_SYS_BIC"."My-Schema/MY_ANALYTICAL_VIEW"

WHERE ("NAME" IN (:varname)) GROUP BY "NAME";

END;

/********* End Procedure Script ************/

Now my call statement is  -

CALL "_SYS_BIC"."My-Schema/NAME_SEARCH"('JIM');

but throws the syntax error again. So, what is expecting? double quotes and no quotes does not work either.

Thanks!

[Former Member · Accepted solution]

Hi,

It should work!!

Try the following: CALL "_SYS_BIC"."My-Schema/NAME_SEARCH"(?);

It should then prompt for Parameter1 : here you give the input value and execute..

I create the following simple procedure wiht input variable: pmt

/********* Begin Procedure Script ************/

BEGIN

select PAYMENTMODE,FEES from CIS_ENROLL

  where "PAYMENTMODE"=:pmt;

END;

/********* End Procedure Script ************/

I am able to successfully call this as:

call "_SYS_BIC"."rp/PROC_WITH_INPUT_SCALAR"('Cash');

or

call "_SYS_BIC"."rp/PROC_WITH_INPUT_SCALAR"(?);

Regards, Rahul

[Former Member · Accepted solution]

Which release of BO/HANA are you on?

I tried both of them but no use. I even made sure the "Name" field type and length in my procedure are in line with the cofiguration in the analytical view (Name is defined as VARCHAR 23)

Tried

CALL "_SYS_BIC"."My-Schema/NAME_SEARCH"(?);

and

CALL "_SYS_BIC"."My-Schema/NAME_SEARCH"('JIM');

In both cases I receive the same syntax error at the same line number/column number/position but i am assuming it is just pointing to the procedure, that's why the same position.

Error : "SAP DBTech JDBC: [1281] (at 1257492544): wrong numbers or types of parameters in call: my-schema/NAME_SEARCH: line 1, column 17 (at position 16)"

If you see anything obvious, please let me know. I am going to try with a different datatype as an i/p and see it makes a difference.

Thanks!

[Former Member · Accepted solution]

Hi

You seem to be giving ',' after BEGIN , can you remove that and try once.

Awaiting your feedback.

Thanks

Santosh

[Former Member · Accepted solution]

Sorry! that was just a typo. I don't have a ',' after BEGIN. Also, I was able to successfully activate the procedure. I am hitting issues when i am calling the procedure in SQL editor. For some reason it does not like the parameters I am entering.

Thanks!

[Former Member · Accepted solution]

Hi MM,

I forgot that there was a var_out defined as output parameter in your code. Please try:

CALL "_SYS_BIC"."My-Schema/NAME_SEARCH"('JIM',?);

or

CALL "_SYS_BIC"."My-Schema/NAME_SEARCH"(?,?);

If you have defined both 1 input and 1 output parameter the procedure expects the above format. Please check SQL scripting guide for all these details.

Regards, Rahul

[Former Member · Accepted solution]

Hi Rahul,

This worked for me!

CALL "_SYS_BIC"."My-Schema/NAME_SEARCH"(?,?);

I searched the SQL script guide for procedure calls but did not see this detail,  then again i was doing a specific search on this so may have missed it.

Thanks a LOT for your help! I'll do a better job of seaching the SQL script guide.

Thanks!

[former_member286530 · Accepted solution]

Hi MM,

   I think sometimes you have several output parameters, you can use 'null' to skip some output  parameters.

Regards,

Rongbo

[henrique_pinto]

From the looks of your procedure code, you have an additional quote after "VAR_OUT", make sure to remove it.

Also, please post here the code you've used to create your procedure.