Creating Stored Procedures

Craig Cmehil

09/13/2018

Intermediate

15 min.

Leveraging SQLScript in Stored Procedures & User Defined Functions

You will learn

In this exercise you will create a small procedure get_po_header_data with two implicit SELECT queries.
Please note - This tutorial is based on SPS11

Step 1: Create new procedure

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

Enter the name of the procedure as get_po_header_data. Click Create

The editor will then be shown.

Step 2: Change namespace name

Change the namespace from “Undefined” to dev602.procedures

Step 3: 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.

Step 4: Review complete code

The completed code should look similar to this. If you do not wish to type this code, you can reference the solution web page at http://<hostname>:51013/workshop/admin/ui/exerciseMaster/?workshop=dev602&sub=ex2_10

PROCEDURE "dev602.procedures::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 "dev602.data::PO.Header" WHERE PURCHASEORDERID IN (
                     SELECT PURCHASEORDERID
                          FROM "dev602.data::PO.Item"
          WHERE "PRODUCT.PRODUCTID" IS NOT NULL)
GROUP BY  "HISTORY.CREATEDBY.EMPLOYEEID";

SELECT COUNT(*) AS CHANGE_CNT, "HISTORY.CHANGEDBY.EMPLOYEEID"
     FROM "dev602.data::PO.Header"  WHERE PURCHASEORDERID IN (
                     SELECT PURCHASEORDERID
                          FROM "dev602.data::PO.Item"
          WHERE "PRODUCT.PRODUCTID" IS NOT NULL)
GROUP BY  "HISTORY.CHANGEDBY.EMPLOYEEID";
END

Step 5: Save and build

Save the procedure.