Skip to Content

Create Stored Procedures

Leveraging SQLScript in Stored Procedures, User Defined Functions, and User Defined Libraries
You will learn
  • How to create a small procedure get_po_header_data with two implicit SELECT queries
rich-heilmanRich HeilmanJanuary 5, 2021
Created by
ccmehil
September 5, 2016
Contributors
rich-heilman
ccmehil

Prerequisites

  • This tutorial is designed for SAP HANA on premise and SAP HANA, express edition. It is not designed for SAP HANA Cloud.
  • Step 1

    Right click on the procedures folder and choose New, then Procedure.

    New Procedure

    Enter the name of the procedure as get_po_header_data. Click Create

    Create

    The editor will then be shown.

    Sample
  • Step 2

    Between the BEGIN and END statements, insert the SELECT statements as shown. These are implicit select statements whose results sets are passed to the caller.

    SELECT COUNT(*) AS CREATE_CNT, "HISTORY.CREATEDBY.EMPLOYEEID"
         FROM "PO.Header" WHERE PURCHASEORDERID IN (
                         SELECT PURCHASEORDERID
                              FROM "PO.Item"
              WHERE "PRODUCT.PRODUCTID" IS NOT NULL)
    GROUP BY  "HISTORY.CREATEDBY.EMPLOYEEID";
    
    SELECT COUNT(*) AS CHANGE_CNT, "HISTORY.CHANGEDBY.EMPLOYEEID"
         FROM "PO.Header"  WHERE PURCHASEORDERID IN (
                         SELECT PURCHASEORDERID
                              FROM "PO.Item"
              WHERE "PRODUCT.PRODUCTID" IS NOT NULL)
    GROUP BY  "HISTORY.CHANGEDBY.EMPLOYEEID";
    
  • Step 3

    The completed code should look similar to this.

    PROCEDURE "get_po_header_data"( )
       LANGUAGE SQLSCRIPT
       SQL SECURITY INVOKER
       --DEFAULT SCHEMA <default_schema_name>
       READS SQL DATA AS
    BEGIN
    
    SELECT COUNT(*) AS CREATE_CNT, "HISTORY.CREATEDBY.EMPLOYEEID"
         FROM "PO.Header" WHERE PURCHASEORDERID IN (
                         SELECT PURCHASEORDERID
                              FROM "PO.Item"
              WHERE "PRODUCT.PRODUCTID" IS NOT NULL)
    GROUP BY  "HISTORY.CREATEDBY.EMPLOYEEID";
    
    SELECT COUNT(*) AS CHANGE_CNT, "HISTORY.CHANGEDBY.EMPLOYEEID"
         FROM "PO.Header"  WHERE PURCHASEORDERID IN (
                         SELECT PURCHASEORDERID
                              FROM "PO.Item"
              WHERE "PRODUCT.PRODUCTID" IS NOT NULL)
    GROUP BY  "HISTORY.CHANGEDBY.EMPLOYEEID";
    
    END
    
    
  • Step 4

    Save the procedure.

    Save

    Perform a build on your hdb module.

    Build
  • Step 5

    Switch over to the Database Explorer page and look for your procedure. Right-click on the procedure and choose Generate Call Statement.

    DBX

    A new SQL tab will be opened with the CALL statement inserted. Click the Run button.

    Run
  • Step 6

    The two results are then shown in another tab.

    Results

    Note the execution time.

    Execution time
Back to top