Procedure

SAP HANA, like other database systems, provides stored procedures mechanism to implement the data logic in database layer. This mechanism is more powerful than views and can boost performance further if used cleverly.

1. What is Procedure in SAP HANA?

• A procedure allows you to group the SQL statement into a single block. Stored Procedures are used to achieve certain result across applications. The set of SQL statements and the logic that is used to perform some specific task are stored in SQL Stored Procedures. These stored procedures are executed by applications to perform that task.
• Procedures are reusable processing blocks, and describe a sequence of data transformation.
• Based on SAP HANA SQL Script.
• Procedures can have any number of input and output parameters (scalar or table-like types)
• A procedure can be created as read only (without side-effects) or read-write (with side-effects)
• Sample about a stored procedure:
Create procedure prc_name (in inp integer, out opt                                   "EFASION"."ARTICLE_LOOKUP")
as
begin
  opt = select * 

           from "EFASION"."ARTICLE_LOOKUP" 
           where article_id   = :inp ;
end;



Few more facts on procedure:
• 
  
• The body of a procedure consists of a sequence of statements separated by semicolons
• An intermediate variable, inside a procedure, is not required to be defined before it is bound by an assignment
• A variable name is prefixed by ':'
• Cyclic dependencies that result from the intermediate result assignments or from calling other functions are not allowed
• A Procedure can be created using the SQL editor or using creation wizards available for the different perspectives in the SAP HANA Studio (Modeler and Development perspectives)
Different ways to create a procedure:

There are 3 ways to create a procedure in HANA.
1. Using the SQL editor
2. Using the Modeler wizard in Modeler perspective
3. Using SAP HANA – XS project in “SAP HANA Development” perspective
Prerequisites for Creating Procedure


Before creating a procedure as part of this exercise, we need to create the tables that will be used. Let us do that.
Example Scenario:

Suppose there are 3 tables PRODUCTS, REGION and SALES.



We need to find out sales value for different region. We also need to calculate NET_AMOUNT based on the DISCOUNT. DISCOUNT value will be passed as input parameter.
We will create a procedure to achieve this.


2. Create Tables:
Create Table Type:

We need to create a table type, which will be used for output parameter of the procedure.
Execute the below SQL statement.


CREATE TYPE SCHEMA_NAME.TT_SALES AS TABLE (

            SALES_AMOUNT DECIMAL,
            NET_AMOUNT DECIMAL,
            PRODUCT_NAME NVARCHAR(20),
            REGION_NAME NVARCHAR(20),
            SUB_REGION_NAME NVARCHAR(20)
);




Create Procedure Using the SQL editor


Syntax:
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
• • READS SQL DATA marks the procedure as being read-only, side-effect free i.e. the procedure does not make modifications to the database data or its structure. This means that the procedure does not contain DDL or DML statements, and that the procedure only calls other read-only procedures. The advantage of using this parameter is that optimizations are available for read-only procedures.
• • Implementation LANGUAGE can be specified. Default is SQLScript.
• • WITH RESULT VIEW is used to create a column view for the output parameter of type table. When a result view is defined for a procedure, it can be called by an SQL statement in the same way as a table or view. 
• Example - Using a result view
  You create a procedure using a result view ProcView to return its results.
  

   CREATE PROCEDURE ProcWithResultView(IN id INT, OUT o1 CUSTOMER)
   LANGUAGE SQLSCRIPT
   READS SQL DATA WITH RESULT VIEW ProcView AS
   BEGIN
     o1 = SELECT * FROM CUSTOMER WHERE CUST_ID = :id;
   END;
  

  You call this procedure from an SQL statement as follows.
  

   SELECT * FROM ProcView (PLACEHOLDER."$$id$$"=>'5');
3. Steps to Create The Procedure:


1. Open SQL Editor and execute the below script
   CREATE PROCEDURE SCHEMA_NAME."PROCEDURE_SALES_REPORT"(
               IN DISCOUNT INTEGER,
               OUT OUTPUT_TABLE SCHEMA_NAME."TT_SALES" )
   LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS
   /*********BEGIN PROCEDURE SCRIPT ************/
   BEGIN
   
   var1 =  SELECT T1.REGION_NAME, T1.SUB_REGION_NAME, T2.PRODUCT_ID, T2.SALES_AMOUNT
               FROM SCHEMA_NAME.REGION AS T1
                          INNER JOIN
               SCHEMA_NAME.SALES AS T2
               ON T1.REGION_ID = T2.REGION_ID;
   
   var2 = SELECT T1.REGION_NAME, T1.SUB_REGION_NAME, T1.PRODUCT_ID, T1.SALES_AMOUNT, T2.PRODUCT_NAME
               FROM :var1 AS T1
                          INNER JOIN
               SCHEMA_NAME.PRODUCT AS T2
               ON T1.PRODUCT_ID = T2.PRODUCT_ID;
   
   OUTPUT_TABLE = SELECT SUM(SALES_AMOUNT) AS SALES_AMOUNT, SUM( SALES_AMOUNT - (SALES_AMOUNT * :DISCOUNT/ 100)) AS NET_AMOUNT,
               PRODUCT_NAME, REGION_NAME, SUB_REGION_NAME
               FROM :var2
               GROUP BY PRODUCT_NAME, REGION_NAME, SUB_REGION_NAME;
   
   END;
2. Refresh the procedure folder under schema in the left Systems tab. You will see the created procedure there.
   
   
3. We call procedure using CALL statement. Execute the below statement to call this procedure.
   CALL SCHEMA_NAME."PROCEDURE_SALES_REPORT" (10, null);
   
   
   
   For table output parameters it is possible to either pass a (temporary) table name or to pass NULL. The option NULL will display the output directly on the client output screen.
4. How to change the store procedure?
You can use ALTER PROCEDURE if you want to change the content and properties of a procedure without dropping the object.

ALTER PROCEDURE [()] [LANGUAGE ] [DEFAULT SCHEMA ] [READS SQL DATA] AS 
BEGIN [SEQUENTIAL EXECUTION]
END