Create Stored Procedures

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.

Right click on the src folder and choose New Folder.

Enter the name of the folder as procedures and click OK.

Click View then Command Pallette.

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.

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

Once again, click Deploy.

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.