Creating with the SQL editor (in SAP HANA Studio)

The following syntax is used to create procedure via the SQL editor:

CREATE PROCEDURE {schema.}name 
            {({IN|OUT|INOUT} 
                        param_name data_type {,...})} 
            {LANGUAGE <LANG>} {SQL SECURITY <MODE>} 
            {READS SQL DATA {WITH RESULT VIEW <view_name>}} AS 
BEGIN 
... 
END

The parameters are for:

Let's create a procedure where we will pass discount as the input parameter and get the sales report as the output parameter. We use the same tables that we created previously:

CREATE PROCEDURE HANA_DEMO."PROC_EU_SALES_REPORT"(
            IN DISCOUNT INTEGER,
            OUT OUTPUT_TABLE HANA_DEMO."EU_SALES" )
LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS
/*********BEGIN PROCEDURE SCRIPT ************/
BEGIN
Pvar1 = SELECT T1.REGION_NAME, T1.SUB_AREA, T2.PRODUCT_KEY, T2.AMOUNT_SOLD
            FROM HANA_DEMO.REGION AS T1
            INNER JOIN
            HANA_DEMO.SALES_FACT AS T2
            ON T1.REGION_KEY = T2.REGION_KEY;

Pvar2 = SELECT T1.REGION_NAME, T1.SUB_AREA, T1.PRODUCT_KEY, T1.AMOUNT_SOLD, T2.PRODUCT_NAME
            FROM :Pvar1 AS T1
            INNER JOIN
            HANA_DEMO.PRODUCT AS T2
            ON T1.PRODUCT_KEY = T2.PRODUCT_KEY;

OUTPUT_TABLE = SELECT SUM(AMOUNT_SOLD) AS AMOUNT_SOLD, SUM(AMOUNT_SOLD - (AMOUNT_SOLD * :DISCOUNT/ 100)) AS NET_AMOUNT,
            PRODUCT_NAME, REGION_NAME, SUB_AREA
            FROM :Pvar2 
            GROUP BY PRODUCT_NAME, REGION_NAME, SUB_AREA;
END;

We can call the previously created procedure with the following CALL statement:

CALL HANA_DEMO."PROC_SALES_REPORT" (8, null);

You can see the created procedure below our schema under the Procedure... folder.

Procedure creation using the wizard

Choose the package in which you want to create the procedure and right-click on it.

A new screen will pop up; fill in the details and click on Confirm:

The SQL console opens with default syntax; we need to put our logic in between BEGIN and END.

The following is a sample logic with which I am creating the Procedure:

On the left-hand side of the screen, you can see the output pane:

Click on it and select New…:

Define the columns which we used in the preceding procedure:

Similarly, perform the same steps for input parameters as well:

Now the procedure is ready to be called via the CALL statement.

Once we build our concept about different views, then one question that will definitely come to our mind is, should we use calculation views (not yet discussed) or procedures. We will discuss this once we have discussed the calculation view in Chapter 5, Creating SAP HANA Artifacts – Analytical Privileges and Calculation Views.