Create Stored Procedures

0 %

Create Stored Procedures

Details

Create Stored Procedures

Rich Heilman

February 12, 2021

Created by

February 1, 2021

Intermediate

15 min.

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

Prerequisites

This tutorial is designed for SAP HANA Cloud.
You have created a BTP trial, SAP HANA Cloud instance. Deploy SAP HANA Cloud trial
You are logged in to SAP Business Application Studio and its configured Configure the Development Environment

Step 1: Import Tables & Data

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.
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.
Right click on the src and choose Import Project, choose the data.zip file that you have downloaded.
The imported files should now show in the newly created data folder.
In the SAP HANA Projects section, click the Deploy button.
You will then see the deployment log.
Next, open the Database Connections folder, and click the Open HDI Container button.
The SAP HANA Database Explorer will be opened, click on the Tables folder, and view the database tables that have been created.

Done

Log on to answer question

Step 2: Create New Procedure

Right click on the src folder and choose New Folder.
Enter the name of the folder as procedures and click OK.
Click View then Find Command...
Enter hana as the search term, then click on SAP HANA: Create SAP HANA Database Artifact
Click the Browse icon.
Select the procedures folder that you created earlier, then click Open.
Use the dropdown, and select Procedure as the Artifact Type.
Give the name of the artifact as get_po_header_data, then click Create.
You will see a message saying that the new artifact as been created.
From the procedures folder, click on your new procedure. The SQLScript procedure editor will then be opened with the shell of your procedure code.

Done

Log on to answer question

Step 3: Add Code and Deploy

View Full Instructions

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, "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";

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, "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

Save the procedure.
Once again, click Deploy.

Done

Log on to answer question

Step 4: Run Procedure and Check Results

Switch over to the Database Explorer page and click on the Procedures folder. Right-click on the procedure and choose Generate Call Statement.
A new SQL tab will be opened with the CALL statement inserted. Click the Run button.
The two results are then shown in another tab.
Note the execution time on the Messages tab.