SAP HANA procedures and optional parameters

[marcus_bhme]

Hi,

I'm looking for a possibility to create a procedure in HANA with optional parameters. Haven't found anything in the docu so a short qeuestion to the this round.

Is anyone aware of a solution for that?

Thx and Regards,

Marcus

[Former Member]

Hi Marcus,

Could you try 'call "mySchema"."MyProcedure"('context_p1', '', '',?)'? it maybe can work.

Hope this can help you.

Regards,

Jerry

[marcus_bhme]

I already tried that one. But than the window with the input field for param_4 is popping up.

What I'm looking for is something like

call "mySchema"."MyProcedure"(Param_1='context_p1');

Regards

Marcus

[Former Member]

Hi Marcus,

You can try call "mySchema"."MyProcedure"(Param_1=>'context_p1'); others are same like this.

Hope this can help you.

Regards,

Jerry

[marcus_bhme]

Nope. Sry.

SAP DBTech JDBC: [1281]: wrong number or types of parameters in call: Param_2 is not bound: line 1 col 15 (at pos 14)

I guess - if a solution exists - is the create procedure statement where the parameter can be filled with a default value or marked as optional like

CREATE

PROCEDURE mySchema.MyProcedure(IN Param_1 nvarchar(100) := '', IN Param_2 nvarchar(100) := '', [...])

[former_member184768]

Hi Marcus,

Is it not possible for you to take the input parameter values anyway and then NOT use them in the procedure depending upon the value or logic ?

Regards,

Ravi

[marcus_bhme]

Hi Ravi,

That's how I realized it right now. I hoped that it will be possible because I have about 20 different values I'm entering in Param_1 as an action_type and depending on that the other params are required and I wouldn't like to "fullfil" the unused parameters with ''.

I now it from other databases (e.g. M$) that the parameters can be defined as optional or with default values.

Regards,

Marcus

[former_member184768]

Hi Marcus,

If you have the SAP account manager associated with your SAP HANA project, then I'd suggest you to raise a "Feature Request". Depending on the business criticality of the required feature, your SAP account manager can then take it up with the proper development Manager / Team.

Regards,

Ravi

[RichHeilman]

You can mimic having optional parameters in your procedures, but only by specifying default values for the parameters.  See this code below.

So here I define two parameters, both have a default value.

create procedure test( in im_var1 nvarchar(10) default 'This',

                          im_var2 nvarchar(10) default 'That'  )

          LANGUAGE SQLSCRIPT

          SQL SECURITY INVOKER

          READS SQL DATA AS

BEGIN

select :im_var1 from dummy;

select :im_var2 from dummy;

END;                        

Next, I can call the procedure and omit any parameter that has a default value. Of course, I am always able to override the default and specify the value to pass to any parameter.  In this case I am passing 'Other' to the second input parameter and omitting the first parameter.

call test( im_var2 => 'Other' );

Hope this helps.

Cheers,

Rich Heilman

[marcus_bhme]

Hi Rich,

Thanks for your idea, but seems it's not working for design-time-procedures (in Rev 61).

If I try to use the default command I got an error message:

"Syntax error, "default" is incorrect or misplaced."

Do you have another idea?

Cheers,

Marcus

[RichHeilman]

Hmmm, that's supposed to be fixed.  I will report this to development.

Cheers,

Rich Heilman

[marcus_bhme]

Hi Rich,

Thanks, that would be great. So I have to wait for a newer revision.

Cheers,

Marcus

[patrickbachmann]

Do you want to create a simple procedure and then call the procedure from something like SQL editor and pass input parameters? Is that what you mean?  If so it's something like this;

SIMPLE PROCEDURE

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

BEGIN

var_out = SELECT YourTable.Field1

//OUTPUT VARIABLE THAT MUST EXIST IN OUTPUT PANE OF PROCEDURE//

FROM "YourSchema"."YourTable"

WHERE YourTable.Field2 = :the_input

//INPUT VARIABLE THAT MUST EXIST IN INPUT PANE OF PROCEDURE//

END

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

CALLING YOUR SIMPLE PROCEDURE FROM SQL EDITOR

call "_SYS_BIC"."YourPackageNameHere/YourProcedureNameHere" ('ValueToPassToProcedureHere',?)

//This 'ValueToPassToProcedureHere' is what will be passed to :the_input input parameter of the procedure.//

Hope this helps.

-Patrick

[marcus_bhme]

Hi Patrick,

thank's for answering. I'm aware of having the parameters and passing them through.

So I have a procedure (created as design-time-object)

CREATE

PROCEDURE mySchema.MyProcedure(IN Param_1 nvarchar(100), IN Param_2 nvarchar(100), IN Param_3 nvarchar(100), IN Param_4 NVARCHAR(100))

LANGUAGE SQLSCRIPT SQL

SECURITY DEFINER AS                 

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

BEGIN

[...]

END;

Now I wan't to call it once with the Param_1 only another time filled up with Param_1 to Param_3

So if I'm calling this procedure that way

call "mySchema"."MyProcedure"('context_p1', '', '');

I got this error messsage (of cause).

Could not execute 'call "mySchema"."MyProcedure"('context_p1', '', '')'

SAP DBTech JDBC: [1281]: wrong number or types of parameters in call: PARAM_4 is not bound: line 1 col 15 (at pos 14)

So how to get these input parameters optional?

Thx

Marcus

[former_member184768]

Hi Marcus,

If you are on SPS 06, potentially you can use something like:

call "mySchema"."MyProcedure"(Param_1 => 'context_p1', Param_2 => '', Param_3 => '', Param_4 => '');

But for the behavior to make it optional, you can also implement that logic in the procedure code by checking the value of the received parameter and then deciding to use it or not.

Regards,

Ravi

[rindia]

Hi Marcus,

Your procedure has 4 input parameters without any output parameter.

So your call to procedure must have 4 parameters.

CALL "mySchema"."MyProcedure"('context_p1', '', '','');

Regards

Raj