Call a Table Function Inside a Stored Procedure OR Vice Versa in SAP HANA

[narasingha]

Hello Friends,

I am writing this blog post on Calling a Table Function Inside a Stored Procedure OR Vice Versa in SAP HANA.

As you all know scripted Calculation views are obsolete and Table function is the replacement for scripted CVs. in HANA 2.0 we have table function as a node too. Which means just like the we use a projection node & an aggregation node same way we can use a table function node which can take the parameters or arguments and return a scalar value or a string or a table too in some case. This is a powerful feature in SAP HANA.

Now the content of my article is, can we call a table function inside a procedure or vice versa. The answer is yes.

Please read the blog post to understand more on this.

Business Scenario 

In one of the scenarios which I had worked while replicating the WBS element table "PRPS" to Native HANA system using SLT. The scenario we faced is the internal to external conversion while replicating using SLT or we can say data type conversion or the alphanumeric conversion in SAP SLT was not working properly and it was causing join failures on HANA data models which we developed further. Without wasting much time on Fixing that on SLT it was advised to write our own custom function on HANA which will take care of the conversion plus update the data in the table.

The ' - ' between the WBS elements was not coming up properly, spaces are added after the replication etc etc and it was causing an issue while creating model for reporting purpose.

SAP ABAP, BW or BW4HANA has alphanumeric conversion function module which will convert the data into required format & takes care of the internal to external conversions.

As the business wanted real time data and analytics solution we ended up writing a stored procedure which will insert records to a table by calling the table function and scheduled the stored procedure with the help of XS job schedule and later scheduled the stored procedure to run on every 30 minutes in SAP BPA by redwood.

So How we did it.

We have written a stored procedure which will eventually call a table function inside it.

My intention here is not to show how the conversion code is written inside the table function as that is custom logic and differs from  business to business but to show how can we call a TF inside a stored procedure.

 

Please see the below diagram here :

 

This is the best way to call a table function inside a Store Procedure.

Now can we call a stored procedure inside a table function which is then used in a graphical calculation view

Answer is Yes.

You can call a procedure within a table function as long as the procedure is read only.a procedure has only IN, OUT, IN OUT parameters.

Code.

The Above code does not work on lower versions on HANA. Please get this tested from your side.

 

Now a question can arise in our mind can we call a stored procedure inside a stored procedure.

 

The answer is obvious and it is yes. We can all as many as procedures inside a procedure and can use the table types as well inside another procedure with in the main procedure.

Thank you.

 

Narasingha Prasad Patro

SAP HANA XSA