Example needed for input and output parameters on Stored procedure

[Former Member]

Hi,

I am a bit new to SAP HANA development and have practised on the standard GUI features. I've been reading into SQL and need you help in understanding something.

Can you please guide me with an example of a stored procedure that uses both input and output parameters? I mean... a simple step by step procedure guideline..

Also ,please let me know how the CALL statement varies when you have lets say 2 input parameters and 3 outputs..

Any help would  be greatly appreciated.

Thanks,

Sam

[Former Member · Accepted solution]

Thanks & I tried this out and got the output.

The procedure I build is the one below:

Thanks for the help.

Now I have 2 questions.

1. Can you tell me what would the corresponding CE logic be for the above code?

    I mean what would the code be considering the same Input and output parameters.

2. I was trying out a simple CE function based on some document that I found. It's giving me errors and I can't understand how to make it work. Please help me out by telling me what is wrong here.

I am just trying to do a simple select which should have some output parameters.

[former_member182302 · Accepted solution]

Hi Sammy,

You may have to use CE_COLUMN_TABLE and CE_JOIN to get the equivalent of your SQL query.

What are the errors you are receiving? Could you share them to help you to resolve the errors?

Have a look on this blog, where i used CE Functions, which may help you to give some idea.

Regards,

Krishna Tangudu

[former_member201560 · Accepted solution]

Hi Sammy,

By looking into your SQL script, the equivalent CE funtion can be

out_tab =  CE_JOIN("EMP_DETAILS","EMP_RATING", [EMPID, EMPID], [EMP_ID, RATING, CITY]).

Please have a look on all available CE functions with relevant examples for better understanding.

Some of the useful CE functions available in the below screenshot:

Regards,

Yogesh

[Former Member · Accepted solution]

Hi Yogesh,

Below is the code that you suggesed.

By looking into your SQL script, the equivalent CE funtion can be

out_tab =  CE_JOIN("EMP_DETAILS","EMP_RATING", [EMPID, EMPID], [EMP_ID, RATING, CITY]).

Now I have 2 questions,

1. As you saw in my first screenshot that city has to be an input parameter used in the where clause.

How will that be handled?

2. If I had let's say join based on 2 fields and not the empID alone, how would you vary the code. For example the join should be based on EMPID and CITY.

Let me know

Thanks

[former_member182302 · Accepted solution]

Hi Sammy,

Sammy Salvatore wrote:

1. As you saw in my first screenshot that city has to be an input parameter used in the where clause.
How will that be handled?

You need to use CE_PROJECTION for it.

CE_PROJECTION(<var_table>, <projection_list>[, <filter>])

Sammy Salvatore wrote:

2. If I had let's say join based on 2 fields and not the empID alone, how would you vary the code. For example the join should be based on EMPID and CITY.

Syntax:

CE_JOIN (<left_table>, <right_table>, <join_attributes> [<projection_list>]) 

Note: The joining column names from the both tables must be of same name.

See a sample here: ( Data am using the same as BLAG mentioned in his blog the link which i shared above)

DROP PROCEDURE PROCWITHRESULTVIEW;

CREATE PROCEDURE ProcWithResultView(IN lt_employee EMPLOYEE,IN lt_role ROLE,
OUT EMPLOYEE_ROLE EMPLOYEE_ROLE)
LANGUAGE SQLSCRIPT READS SQL DATA WITH RESULT VIEW ProcView AS
BEGIN
EMPLOYEE_FILTER = CE_PROJECTION(:lt_employee,["ID","NAME","LAST_NAME","ROLE_ID"],'"ID" > 3'); -- To do filtering
EMPLOYEE_ROLE = CE_JOIN(:EMPLOYEE_FILTER,:lt_role,["ROLE_ID"],["NAME","LAST_NAME","ROLE_ID","ROLE"]); -- To Join
END;

call ProcWithResultView ('EMPLOYEE','ROLE',?)

Also the syntax will be available by using "F1" in the studio, which also shares some examples.

Regards,

Krishna Tangudu

[shyam_uthaman · Accepted solution]

Hi Sammy,

i tried to recreate the program that you are trying to do in my HANA system.

Below is the SQL that you are using translated to a CE version:

About your second question, use CE_COLUMN_TABLE is the function you are looking for .. not  CE_COLUMN_TAB

[Former Member · Accepted solution]

Thanks Shyam.. this is what I was looking for.

Even though I found the functions, I was struggling with small issues in the syntax.

Thanks everyone for helping out.

Cheers!

[Former Member]

Hi,

Thanks for those links but as a beginner, that just went over my head.. I need something a bit simpler like has posted.

I had some questions on the example that h had posted.

The call statement used here is call prc_PO_Details('TEST_MATERIAL','1234',null);

1. I see that you have specified output parameter as null. Can you please elaborate on why this has been done? If this was done to give just a blank table for the output to be stored in, is there any scenario where I can put something else there except the null?

2. What if I want to supply a list of values for both the input parameters? How would the code change?

Please let me know.

Thanks,

Sam

[former_member182302]

Hi Sam,

As a starter i can suggest you the below document,which helped me a lot to get myself (being a BW consultant) to get acquainted with basic sql in SAP HANA.

SQLScript Small Guide | SCN

Sammy Salvatore wrote:

The call statement used here is call prc_PO_Details('TEST_MATERIAL','1234',null);
1. I see that you have specified output parameter as null. Can you please elaborate on why this has been done? If this was done to give just a blank table for the output to be stored in, is there any scenario where I can put something else there except the null?

You can even use call prc_PO_Details('TEST_MATERIAL','1234',?); (? or null implies the same)

It just denotes that there is an output parameter and it helps in displaying the result.

2. What if I want to supply a list of values for both the input parameters? How would the code change?

When you meant 'list of values' , you meant multiple values in filter condition isn't? If that is the case you have 2 options

1) Using EXEC:

Create a temporary table and use EXEC to dynamically frame your select query and to execute it. (if required let me know i will explain it further )

2) Using EXECUTE IMMEDIATE:

SAP HANA: Handling Dynamic Select Column List and Multiple values in input parameter

Regards,

Krishna Tangudu

[former_member201560]

Hi Sammy,

Please find the comments from my side

1. Since, I am calling this stored procedure as a simple SQL script, we cannot have the variable names in input/output. But, programmatically, you can update input variables and output variables. Remember you can have output in two ways, a) Normal output variable, b) Table type. The above example deals with output of table type. In my example, I have created the table type (tt_purchase), The output of the select statement will be stored in this table(tt_purchase).

2. If you want to pass the list of values, you can create the table type and insert the data in the table type and pass the table type name in the input parameter

(ex) call prc_tt_test(tt_order, tt_site, null)

where tt_order and tt_site are the two tables(table types) where you can insert your data into the tbale type and give input to the stored procedure.

Regards.

Yogesh.

[former_member201560]

Dear Sammy,

Please find some simple stored procedures examples which I used when I started learning to write stored procedures in HANA.

1. To display PO details if MATNR and WERKS(site) given
=============================================

Creating table type:

create type tt_purchase AS table
(
EBELN NVARCHAR(10),--EKPO.EBELN,
MATNR NVARCHAR(18),--EKPO.MATNR,
BUKRS NVARCHAR(4),--EKPO.BUKRS,
WERKS NVARCHAR(4),--EKPO.WERKS,
EKORG NVARCHAR(4),--EKKO.EKORG,
WAERS NVARCHAR(6)--EKKO.WAERS
);

Creating stored procedure:
create procedure prc_PO_Details(IN MATNR NVARCHAR(18), IN WERKS NVARCHAR(4), OUT RES tt_purchase)

LANGUAGE SQLSCRIPT READS SQL DATA WITH RESULT VIEW EKKOVIEW AS

BEGIN

RES = SELECT B.EBELN, B.MATNR, B.BUKRS, B.WERKS,A.EKORG,A.WAERS
FROM EKKO A , EKPO B
WHERE A.EBELN = B.EBELN
AND B.WERKS = :WERKS
AND B.MATNR like :MATNR;

END;

Calling the above stored procedure with inputs and outputs:

call prc_PO_Details('TEST_MATERIAL','1234',null);

2. Using cursors:

==============

CREATE PROCEDURE cursor_test(IN article VARCHAR(20)) LANGUAGE SQLSCRIPT AS

val decimal(34,10) := 0;

CURSOR c_cursor1 FOR

SELECT "article_no", "store_no", sum("qty"), sum("price") as "price"

FROM article_perish

where "article_no" = :article

group by "article_no", "store_no"

order by "article_no", "store_no";

BEGIN

FOR r1 AS c_cursor1

DO

  val := :val + r1."price";

END FOR;

select val from dummy;

END;

call cursor_test('A001')

Note: The best practice is to avoid usage of cursors in the stored procedures, because you will get performance issue if the data volume is large.

Regards,

Yogesh

[former_member182302]

Hi Sam,

Yes, you can have a procedure with any number of input parameters and output parameters. For the case you mentioned ex: call Procedure_name ( Input1,Input2,Input3,?,?)

Have a look on the sample procedure here:

Please note that i've not used out parameter in the last one, since i was using EXECUTE IMMEDIATE.

If you are using EXEC, then you would need an output parameter to hold the result.

Regards.

Krishna Tangudu