Skip to Content
Previous

Creating Stored Procedures

By Craig Cmehil

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.

Details


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

    New Procedure
  2. Enter the name of the procedure as get_po_header_data. Click “Create”

    Create
  3. The editor will then be shown.

    Sample
  4. Change the namespace from ���Undefined��� to dev602.procedures

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

```

  1. Save the procedure.

    Save
  2. Perform a build on your hdb module.

    Build
  3. Switch over to the HRTT page and look for your procedure

    HRTT
  4. Right-click on the procedure and choose “Invoke Procedure”.

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

    SQL tab
  6. Click the “Run” button.

    Run
  7. The two results are then shown in another tab.

    Results
  8. Note the execution time.

    Execution time

Next Steps

Updated 12/08/2016

Time to Complete

15 Min.

Intermediate
Next
Back to top