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 24, 2022
Created by
rich-heilman
February 1, 2021
Contributors
rich-heilman

Prerequisites

  • Step 1
    1. If you have not done so yet, create a new HANA Database Project in the SAP Business Application Studio. For more information about how to do this, complete step 1 from the Create an SAP HANA Database Project tutorial.

    2. Next, download the data.zip file from here. We will use predefined tables with sample data for all of the SQLScript tutorials in this group.

    3. Right click on the src and choose Import Project, choose the data.zip file that you have downloaded.

      Data.zip
    4. The imported files should now show in the newly created data folder.

      Imported Files
    5. In the SAP HANA Projects section, click the Deploy button.

      Deploy
    6. You will then see the deployment log.

      Deployment Log
    7. Next, open the Database Connections folder, and click the Open HDI Container button.

      Open HDI Container
    8. The SAP HANA Database Explorer will be opened, click on the Tables folder, and view the database tables that have been created.

      Tables
  • Step 2
    1. Right click on the src folder and choose New Folder.

      New Folder
    2. Enter the name of the folder as procedures and click OK.

      Create Folder
    3. Click View then Find Command...

      Find Command
    4. Enter hana as the search term, then click on SAP HANA: Create SAP HANA Database Artifact

      HANA Archifact
    5. Click the Browse icon.

      Browse
    6. Select the procedures folder that you created earlier, then click Open.

      Select Procedure
    7. Use the dropdown, and select Procedure as the Artifact Type.

      Artifact Type
    8. Give the name of the artifact as get_po_header_data, then click Create.

      Procedure Name
    9. You will see a message saying that the new artifact as been created.

      Message
    10. From the procedures folder, click on your new procedure. The SQLScript procedure editor will then be opened with the shell of your procedure code.

      Editor
  • Step 3
    1. 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.

      SQLCRIPT
      Copy
      SELECT COUNT(*) AS CREATE_CNT, "CREATEDBY"
           FROM "OPENSAP_PURCHASEORDER_HEADERS" WHERE ID IN (
                           SELECT "POHEADER_ID"
                                FROM "OPENSAP_PURCHASEORDER_ITEMS"
                WHERE "PRODUCT_PRODUCTID" IS NOT NULL)
                  GROUP BY  "CREATEDBY";
      
      SELECT COUNT(*) AS CHANGE_CNT, "MODIFIEDBY"
           FROM "OPENSAP_PURCHASEORDER_HEADERS"  WHERE ID IN (
                           SELECT "POHEADER_ID"
                                FROM "OPENSAP_PURCHASEORDER_ITEMS"
                WHERE "PRODUCT_PRODUCTID" IS NOT NULL)
                   GROUP BY  "MODIFIEDBY";
      
    2. The completed code should look similar to this.

      SQLSCRIPT
      Copy
      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, "CREATEDBY"
           FROM "OPENSAP_PURCHASEORDER_HEADERS" WHERE ID IN (
                           SELECT "POHEADER_ID"
                                FROM "OPENSAP_PURCHASEORDER_ITEMS"
                WHERE "PRODUCT_PRODUCTID" IS NOT NULL)
                  GROUP BY  "CREATEDBY";
      
      SELECT COUNT(*) AS CHANGE_CNT, "MODIFIEDBY"
           FROM "OPENSAP_PURCHASEORDER_HEADERS"  WHERE ID IN (
                           SELECT "POHEADER_ID"
                                FROM "OPENSAP_PURCHASEORDER_ITEMS"
                WHERE "PRODUCT_PRODUCTID" IS NOT NULL)
                   GROUP BY  "MODIFIEDBY";
      
      END
      
      
    3. Save the procedure.

      Save
    4. Once again, click Deploy.

      Save
  • Step 4
    1. Switch over to the Database Explorer page and click on the Procedures folder. Right-click on the procedure and choose Generate Call Statement.

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

      Run
    3. The two results are then shown in another tab.

      Results
    4. Note the execution time on the Messages tab.

      Execution time
Back to top