Skip to Content

Creating Stored Procedures

test
0 %
Creating Stored Procedures
Details
// Explore More Tutorials

Creating Stored Procedures

09/13/2018

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.

New Procedure

Enter the name of the procedure as get_po_header_data. Click Create

Create

The editor will then be shown.

Sample
Step 2: Change namespace name

Change the namespace from “Undefined” to dev602.procedures

Namespace
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.

Enter Code
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.

Save

Perform a build on your hdb module.

Build
Step 6: Invoke procedure

Switch over to the HRTT page and look for your procedure

HRTT

Right-click on the procedure and choose Invoke Procedure.

Invoke Procedure

A new SQL tab will be opened with the CALL statement inserted.

SQL tab

Click the Run button.

Run
Step 7: Check results

The two results are then shown in another tab.

Results

Note the execution time.

Execution time

Next Steps

Next Steps

Back to top