Posted on by

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:

  1. Join tables VBAK and VBAP and pull data for a single material provided by an input parameter.
  2. 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.
  3. 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.

SAP HANA Stored Procedures

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.

SAP HANA Stored Procedures

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.

SAP HANA Stored Procedures

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.

SAP HANA Stored Procedures

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.

SAP HANA Stored Procedures

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.

SAP HANA Stored Procedures

The new code would look as below.

Now, activate this code. It should go smoothly this time.

SAP HANA Stored Procedures

Once done, we call the procedure first with the material number ‘PROD319’.

SAP HANA Stored Procedures

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.

SAP HANA Stored Procedures

To check data of SALES_LOG, write an SQL statement as below.

SAP HANA Stored Procedures

The data below looks correct for the material we used in the input parameter.

SAP HANA Stored Procedures

To check if the DELETE works, let’s call the procedure using a different material – CM_WHEAT this time.

SAP HANA Stored Procedures

The procedure runs again as expected.

SAP HANA Stored Procedures

SAP HANA Stored Procedures

The data preview shows that the old data is gone and only data for the new material number remains.

SAP HANA Stored Procedures

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.

<<Previous Page                                                                                               Next Tutorial>>                                                                                             

Tagged .

149 Comments

  3. I’m facing this error (Could not create catalog object: insufficient privilege; Not authorized). When I am going to create the stored procedure on my own created schema and tables,I have used “grant selection on schema zschema to _sys_repo with grant option ” it ‘s useless .Can you please help me to solve this issue.

        • You probably did not have access to do some operation on the schema you are using in the stored procedure. But anyways, It would be helpful to the others if you share how you resolved the error.

          • Hello,

            is there a resolution to above mentioned problem? In my case as well, I have received the same error about “Insufficient privileges”. I have tried giving GRANT SELECT/EXECUTE to _SYS_REPO but it doesnt seem to be working.

          • Hey Guys,

            Solved this issue.

            Please provide GRANT DELETE on “Your Schema name” to _SYS_REPO as well as
            GRANT INSERT on “Your schema name” to _SYS_REPO.

            As we are deleting and then inserting into one of the catalog object (in this cases SALES_LOG table), user _SYS_REPO needs required priviliges to do those actions.

            @ Shyam – Excellent tutorials. Really Helpful.

          • Done Shyam. This website is clear, concise and simple :). Thank you.
            I would really appreciate if you can keep enhancing it with some of the more trivial analytics questions such as performing YTD, MTD, YoY and different complex use cases that people face in actual projects.

    • Hi Esteban,
      In real projects, you are not allowed, as a developer to add on privileges at will. There are restrictions in place to make sure the developers don’t get too powerful to wreck the system.
      That’s why we try to play around within the bounds of a regular developer role until something really requires additional access – which again would require approvals and subsequent actions from the SAP security team.

      Shyam

  5. Hi Shyam,

    This is the best document I am reading on Stored Procedure in SAP HANA explaining everything in so simple words from A to Z. Awesome, Great work !!

    Regards
    Prasun

    • They are used to do database operations like inserting, deleting , updating data and more along those lines.

  9. Hi Shyam,

    Very good tutorial, thank you.

    Can you explain a real time scenario on how the procedures can get integrated into a calaculation view and how the reporting can be done on this?

    Thanks
    Ramya

  10. I have gone through several tutorials for SQL Script,,, but this is the best it I got. Made the stuff crisp and clear.Thanks for sharing the knowledge. 🙂

  11. awsome.You have really helped me clear my SQL basics plus increase my scripted HANA knowledge.Let me know if any help is required from my end of any sort.

    • It can be used in codes with where the output is not a single value but a table with multiple rows. A table type can be used to define the structure of those tables.

  17. Hi Shyam,

    It would be of great help if you can explain how these stored procedures are consumed in scripted calculation views. with sample example
    Individually the concept of stored procedure is clear however Im not really clear about the next phase like how it can be consumed in real case scenario specially in scripted calculation views

    Thanks,
    CK

  19. When I tried to Call a Delete procedure in a flowgraph it says “is not read only. Only procedures defined with ‘READS SQL DATA’ are supported in task plans.”
    The Procedure in itself works fine.

  21. Dear Shyamuthaman,

    Thanks for your work…. This site has been very intresting and usefull.

    I have a query ?

    how we can transport/export a procedure b/w two clients??/

    If you have a free time please clear my doubt.

    Regards,
    Mahesh B

Leave a Reply

Your email address will not be published. Required fields are marked *