"How to create a small procedure `get_po_header_data` with two implicit SELECT queries"

"What do I need before I start?"

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

Create Stored Procedures

Leveraging SQLScript in Stored Procedures, User Defined Functions, and User Defined Libraries

🎓intermediate ⏱15 min SAP HANA Intermediate Sql Express Edition 0/6

Overview

🎓 intermediate ⏱ 15 min. SAP HANAIntermediateSqlExpress Edition

You will learn

✔How to create a small procedure get_po_header_data with two implicit SELECT queries

R Rich Heilman November 1, 2022

Created by September 5, 2016

Contributors

Prerequisites

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

Steps

Open all Close all

Step 1 Create New Procedure

—

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

Enter the name of the procedure as get_po_header_data. Click Create

The editor will then be shown.

Step 2 Add SELECTs

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.

Code

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 Review Complete Code

The completed code should look similar to this.

Code

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 and Build