SAP HANA Stored Procedures – Part 4
Welcome to the fourth and final part of the SAP HANA Stored Procedures tutorial. If you landed on this page directly, please check out Part 1, Part 2 and Part 3 of this tutorial before you read further. Else the concepts may not make complete sense.
Continuing from where we left off…
Scenario 2 – Stored procedures for other statements like INSERT, DELETE, UPDATE
This is the primary scenario for which SAP HANA stored procedures are mostly used.
Let’s assume that the requirement is to add on to our previous code. But now the logic should follow the requirement as below:
- Join tables VBAK and VBAP and pull data for a single material provided by an input parameter.
- Instead of providing this data in the output when called, the stored procedure should store this data in a custom database table named SALES_LOG.
- Before filling SALES_LOG table, delete all the existing data in it.
So, we already have step 1 coded and ready from our previous example. We now require two more database operations to fulfill this requirement- INSERT (to add new data to the custom table) and DELETE (to delete all data from the SALES_LOG) table before it’s filled again.
The custom table we need to fill has the same structure as our current procedure output as is also shown below.
The below code already has a problem as per our new requirement which we will come to know soon. Have a close look and try to figure it out if you can.
To start with the new requirement, we start adding on now to the existing code from our previous example. The join output previously was also the output of the stored procedure. But in our current scenario, we need no data display at all. We just need a certain set of operations to be done.
So, to start, we capture our entire output from earlier in a temporary table variable called t_sales_data. Imagine this as after the system processes the logic, instead of throwing it to the display, it stores it in a temporary container which only lives as long as the stored procedure is running. As soon as the procedure ends, the container/temporary table variable ceases to exist.
Next, we write a DELETE statement to clear all the data from SALES_LOG table as shown below. This satisfies Point 3 of our requirement. At this point, what our procedure does is that it takes data from the join result and keeps it hanging in mid air in a temporary table variable. Plus it clears data in the SALES_LOG table completely.
In the last step, we need to fill the SALES_LOG table with the data held by this temporary table variable.
To add data, we already know that the INSERT statement is used. Since we already know that the SALES_LOG table has the same field names and order of fields as the join output, we use a quick shortcut.
The statement SELECT * from :t_sales_data pulls all data from the table variable in the same order and field name as delivered by the join result.
The INSERT INTO statement inserts the results from the SELECT statement below it into the SALES_LOG table.
Thus, all three parts of our requirement are now satisfied.
Press the activate button marked below.
This should throw an error message “Syntax error in procedure object: feature not supported; INSERT/UPDATE/DELETE are not supported in the READ ONLY procedure/function: line 21 col 1 (at pos 504)“ . This is due to the problem I pointed out as we started implementing this scenario.
This problem stems from line 4 of our code where it’s written READS SQL DATA. These statements tell HANA that this procedure was only meant to read data and not do any further operations on tables. To fix this, remove these 3 keywords from the code.
The new code would look as below.
Now, activate this code. It should go smoothly this time.
Once done, we call the procedure first with the material number ‘PROD319’.
As seen below, the call did not result in any data output to the screen as there were no open SELECT statements. Our primary operations were DELETE and INSERT which do not do any data outputs to the screen.
To check data of SALES_LOG, write an SQL statement as below.
The data below looks correct for the material we used in the input parameter.
To check if the DELETE works, let’s call the procedure using a different material – CM_WHEAT this time.
The procedure runs again as expected.
The data preview shows that the old data is gone and only data for the new material number remains.
This confirms that the stored procedure worked perfectly.
SAP HANA stored procedures are reusable snippets of code which would be used in other development objects like Calculation views, other stored procedures, table functions and more. As seen from our examples, they can behave dynamically to different inputs and are really powerful tools in efficient modularized implementation.
This concludes our tutorial on SAP HANA stored procedures. Be sure to comment with your thoughts and share this articles on social media to your friends and colleagues.
If you feel the website helped you, please also contribute any small amount by using the “Donate button” on the right side of this page to help with the operational costs of this website and other systems involved.
Thanks for reading.