Pass multiple parameters via IN statement to HANA procedure

[Former Member]

I have a requirement where I need to pass multiple single values to a procedure in HANA -

For example: I create a procedure called HANA_PROCEDURE with following input and output parameters.

• INPUT: is a Scalar Parameter; has  data type VARCHAR and has length = 200

     Document number > VARCHAR(200)

     Account > VARCHAR (10)

HANA_PROCEDURE CODE:

BEGIN

"OUTPUT" = SELECT "DOCUMENT_NUMBER" "ACCOUNT" FROM _SYS_BIC_ANALYTICAL_VIEW

WHERE "ACCOUNT" IN (:INPUT)

GROUP BY "DOCUMENT_NUMBER" "ACCOUNT";

END;

Next i call the procedure -

call "_SYS_BIC"."user/HANA_PROCEDURE"(?,?)

pops up a "Prepared SQL tab" that prompts me to enter the input values. I enter one value =  input1 ....and it returns values

as soon as i enter 'value1', 'value2' it chokes and does not return anything.

NOTE: if i hard code the input values int he procedure code in this exact same format i,e, 'value1', 'value2'....works just fine and returns records for both the values.

I think the internal format for the input variables is getting messed up somehow. ANy ideas how i can get these multiple single inputs to work?

Thank you in advance.

• OUTPUT: Table type with following fields

[rindia]

Hi M M,

This thread looks old and do not know whether you found the solution or not?

Could you please post your complete proc so that I and others can try and give better solution.

@Ravi: Surprise that you digged and found this unanswered thread

Regards

Raj

[Former Member]

The only solution I've ever seen to this problem is to create sub-procedure that parses a delimited string into an internal table (i.e. table variable). This can be done with some crafty string manipulation and a while loop.

(FYI - the input is a string, so that's all the stored procedure 'sees'. It doesn't have any way of 'knowing' that inside of this string is a list of values. It can't interpret it as such.)

If someone would like source code for it let me know and will check with the colleague of mine that provided a solution to this.

[former_member187673]

Hi Jody, please share the code if you have it. Regarding the string formation, is that easy to form at the BOBJ Webi layer if selecting from a list of values before passing to HANA? Have limited knowledge of Webi so appreciate any info.

Thanks

Peter

[Former Member]

Hi Peter,

1) Will check with a colleague. I modified his code a bit but will check with him first before sharing. Might be a week or so... will see if I can get something sooner.

2) I'm not a front-end guy - the two explicit use cases I know about were from App servers - one was XS, the other ABAP - so not quite sure about WebI, sorry.

[Former Member]

Hi Peter, FYI: http://scn.sap.com/docs/DOC-41738

That should get you start. Could be a bit more robust for production code but will give you a good idea about how to accomplish what you need.

[former_member187673]

Thanks Jody, appreciate it.

[Former Member]

Sure thing

[former_member187673]

Hi MM,

Were you able to resolve this? We have a similar requirement i.e. we need to pass multiple single values from client e.g. Web Intelligence to a stored procedure in HANA.

Is this possible?

Thanks

Peter

[shraddha_jalan41]

Hi.. Can you provide solution to pass multiple parameters via IN statement to HNA procedure using while loop?

[former_member184768]

Hi M M,

Just top of the head thought, why don't you pass the input as a string, with multiple values and use "EXEC SQL" with the string SQL statement built with the input values. EXEC SQL will provide you better flexibility in generating the SQL statement.

Regards,

Ravi

[shraddha_jalan41]

Hi.. Can you provide solution to pass multiple parameters via IN statement to HNA procedure using while loop?