SELECT From Stored Procedure on SAP HANA Database
SAP HANA database developers who code SQL using SQLScript can execute SELECT statement from Stored Procedures by using "With Result View" clause in HANA procedure definition.
In this SQLScript tutorial, I want to demonstrate how SAP HANA database programmers can run SELECT statements on output of a HANA procedure and use this data in an INSERT SQL command.
Just like in other RDS (Relation Database Systems) for example SQL Server community, HANA database developers are also looking for ways and methods to query data from procedures (stored procedure).
SQL programmers know that it is possible to pass parameters in two directions, from outside of stored procedure to inside as INPUT parameter and also as returning parameters from procedure to calling SQLScript as OUTPUT parameter.
Using OUTPUT parameter which is defined with a table type can be used to return data in the format of a dataset instead of single scalar values.
On the other hand, in many cases using OUTPUT parameters is not enough for SQL programmers.
In this SAP HANA SQLScript tutorial, I would like to show a few alternative ways of returning table data from HANA procedure one of which enables SQL programmers to execute SELECT query from Stored Procedure directly.
Create HANA Database Stored Procedure with Output Parameter as Table Type
Let's simply create a stored procedure on HANA database and query sales order items for a given order number from SAP table VBAP.
Before we start coding for HANA procedure let me continue with just building a SELECT command to fetch required data.
do
begin
DECLARE lv_vbeln varchar(10) := '1653000008';
SELECT * FROM "SAPABAP1"."VBAP" WHERE VBELN = :lv_vbeln;
end;
After we see that our SQLScript code works quite fine and returns us the desired SAP data, let's now convert this SELECT into a form of a HANA procedure as follows
Create Procedure SalesOrder_Items (
in p_vbeln varchar(10),
out et_items "SAPABAP1"."VBAP"
)
Language SQLScript
reads sql data
as
Begin
et_items = select * from "SAPABAP1"."VBAP" where vbeln = :p_vbeln order by posnr;
End;
Let's call this HANA procedure in a sample SQLScript code block
do
begin
DECLARE lt_order_items "SAPABAP1"."VBAP";
CALL SalesOrder_Items('1653000008', lt_order_items);
SELECT * FROM :lt_order_items;
end;
As programmers will notice, in above SQL code I have just defined a table type variable and pass this with the sales order number to the HANA stored procedure.
Executing HANA procedure is done with "CALL procedureName" syntax by passing two other parameters.
Then using the output table type parameter, I can execute a SQL SELECT query on table parameter and display data as seen in below screenshot.
Maybe an easier way of returning the selected data from HANA procedure and displaying it on screen is as running the CALL procedure command as follows
CALL SalesOrder_Items('1653000008', ?);
Select from Procedure on SAP HANA Database for SQL Developer
Of course if this is not the requirement, using output table type parameters for transferring selected table data like internal tables, and if the SQLScript programmer requires to run SELECT command on HANA procedure, following modifications can be done on the Stored Procedure definition.
Create Procedure SalesOrder_Items (
in p_vbeln varchar(10),
out et_items "SAPABAP1"."VBAP"
)
Language SQLScript
reads sql data
with result view procedure_view_SalesOrderItems
as
Begin
et_items = select * from "SAPABAP1"."VBAP" where vbeln = :p_vbeln order by posnr;
End;
Please note that I added "WITH RESULT VIEW hana_view_name".
This is very important because "with result view" creates a HANA view object with name defined in the command.
So SQLScript developers can query this view in their SQL codes or ABAP programmers can display data from this view.
Just to note here, unfortunately ALTER PROCEDURE command fails when developer adds "WITH RESULT VIEW" syntax eith exception "feature not supported: cannot create result view with ALTER command".
So SQL programmers have to "DROP Procedure" first then "CREATE Procedure" again with additional "WITH RESULT VIEW" syntax.
I deleted sample HANA procedure with "Drop Procedure" command as follows then create procedure again with previous SQLScript code
Drop Procedure SalesOrder_Items;
I know this is not a direct execution of SELECT from procedure on HANA database, but after this modification on SAP HANA database procedure, somehow programmers can query and select data from procedure.
Here is the syntax for SQL developers to query and SELECT data from HANA procedure
SELECT * FROM procedure_view_SalesOrderItems
WITH PARAMETERS ( 'placeholder' = ('$$p_vbeln$$','1653000008'));
And of course it is also possible to add filtering criteria using WHERE clause on HANA procedure too.
Following SQLScript can be considered as querying to SELECT from HANA database procedure with WHERE clause.
It is important actually syntatically where you put filtering WHERE clause
SELECT * FROM procedure_view_SalesOrderItems WHERE Posnr = '000010'
WITH PARAMETERS ( 'placeholder' = ('$$p_vbeln$$','1653000008'))
HANA database SQLScript programmers have probably realized that the sales order document number is passed with a static value, but in most cases developers have to provide such filtering criterias using variables. So let's modify our previous SQL code to use PLACEHOLDER for variables to our HANA procedure's parametric view
do
begin
DECLARE lv_vbeln varchar(10);
lv_vbeln := '1653000008';
SELECT * FROM procedure_view_SalesOrderItems ( placeholder."$$p_vbeln$$" => :lv_vbeln)
WHERE Posnr = '000010';
end;
This time SQL developers will use a different syntax than others we have used in our tutorial.
I removed the WITH PARAMETERS section and used the placeholder, input variable name and its value right after the HANA procedure's view name.
This syntax is more familiar to most of HANA database developers from parametric views.
To summarize, in this SAP HANA database tutorial I want to answer how SQLScript programmers can SELECT data from HANA procedure. The trick is modifying the HANA stored procedure by adding "With Result View" clause and defining the procedures view namme. Then SQL programmers developing SAP application on S/4HANA can query HANA procedure by using its view easily. If the HANA procedure has input parameters, by using the syntax that SQLScript developers use for parametric view, again the HANA procedure can be queried.
