SapHanaTutorial.Com HOME     Learning-Materials Interview-Q&A Certifications Quiz Online-Courses Forum Jobs Trendz FAQs  
     Explore The World of Hana With Us     
About Us
Contact Us
 Apps
X
HANA App
>>>
Hadoop App
>>>
Tutorial App on SAP HANA
This app is an All-In-One package to provide everything to HANA Lovers.

It contains
1. Courses on SAP HANA - Basics, Modeling and Administration
2. Multiple Quizzes on Overview, Modelling, Architeture, and Administration
3. Most popular articles on SAP HANA
4. Series of Interview questions to brushup your HANA skills
Tutorial App on Hadoop
This app is an All-In-One package to provide everything to Hadoop Lovers.

It contains
1. Courses on Hadoop - Basics and Advanced
2. Multiple Quizzes on Basics, MapReduce and HDFS
3. Most popular articles on Hadoop
4. Series of Interview questions to brushup your skills
Apps
HANA App
Hadoop App
';
Search
Stay Connected
Search Topics
Topic Index
+
-
SQLScript
+
-
Tables in SAP HANA
+
-
Procedure
+
-
Examples of Procedure
SAP HANA Procedure - Old Wine in New Bottle!


In this article we will explain What is SQL Procedure? What are the different ways to create procedure in HANA? The pros and cons of procedure in HANA
What is Procedure?

A procedure is a unit/module that performs a particular task. Procedures are reusable processing blocks, and describe a sequence of data transformations Procedures can have multiple input and output parameters (scalar or table types) DROP and CREATE statements are used to modify the definition of a procedure A procedure can be created as read only (without side-effects) or read-write (with side-effects)
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 ':' while used as input to another statement 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. Using the SQL editor Using the Modeler wizard in Modeler perspective Using SAP HANA XS project in "SAP HANA Development" perspective We will learn about each of these approaches in details.

Note: Do not get confused by the questions like - why so many ways to create procedure? Which is one should I use? Which one is better? We will explain these later. Right now let us just learn each of these approaches.

Prerequisites:

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.

SAP HANA Procedure

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.

Create Tables:

Open HANA Studio and expand the SAP HANA system. Go to your schema. Right-click on your schema and select SQL editor.
SAP HANA Studio
Note: In this example schema name is "SAP_HANA_TUTORIAL". In case you want to create a new schema use below query.
create schema <schema_name>;
Copy and paste the below script in SQL editor and execute.

----REPLACE <YOUR SCHEMA> WITH YOUR SCHEMA NAME

---- Create Product table
create column table "<YOUR SCHEMA>"."PRODUCT"(
      "PRODUCT_ID" INTEGER,
      "PRODUCT_NAME" VARCHAR (100),
      primary key ("PRODUCT_ID")
);

insert into "<YOUR SCHEMA>"."PRODUCT" values(1,'Shirts');
insert into "<YOUR SCHEMA>"."PRODUCT" values(2,'Jackets');
insert into "<YOUR SCHEMA>"."PRODUCT" values(3,'Trousers');
insert into "<YOUR SCHEMA>"."PRODUCT" values(4,'Coats');
insert into "<YOUR SCHEMA>"."PRODUCT" values(5,'Purse');
-- Create Region table
create column table "<YOUR SCHEMA>"."REGION"(
      "REGION_ID" INTEGER,
      "REGION_NAME" VARCHAR (100),
      "SUB_REGION_NAME" VARCHAR (100),
      PRIMARY KEY ("REGION_ID")
);

insert into "<YOUR SCHEMA>"."REGION" values(100,'Americas','North-America');
insert into "<YOUR SCHEMA>"."REGION" values(200,'Americas','South-America');
insert into "<YOUR SCHEMA>"."REGION" values(300,'Asia','India');
insert into "<YOUR SCHEMA>"."REGION" values(400,'Asia','Japan');
insert into "<YOUR SCHEMA>"."REGION" values(500,'Europe','Germany');

-- Create Sales table
create column table "<YOUR SCHEMA>"."SALES"(
      "REGION_ID" INTEGER ,
      "PRODUCT_ID" INTEGER ,
      "SALES_AMOUNT" DOUBLE,       PRIMARY KEY ("REGION_ID", "PRODUCT_ID") );

insert into "<YOUR SCHEMA>"."SALES" values(100,1,100);
insert into "<YOUR SCHEMA>"."SALES" values(100,2,90);
insert into "<YOUR SCHEMA>"."SALES" values(100,5,85);
insert into "<YOUR SCHEMA>"."SALES" values(200,2,80);
insert into "<YOUR SCHEMA>"."SALES" values(200,1,75);
insert into "<YOUR SCHEMA>"."SALES" values(300,3,85);
insert into "<YOUR SCHEMA>"."SALES" values(400,4,75);
insert into "<YOUR SCHEMA>"."SALES" values(500,1,65);
insert into "<YOUR SCHEMA>"."SALES" values(500,2,65);
After executing the scripts you should have 3 tables created. If there are no tables, try right-clicking on your schema and refreshing.
SAP HANA Studio
Grant schema SELECT rights to _SYS_REPO user:

Open the SQL editor of your schema and execute the following command line:
GRANT SELECT ON SCHEMA <YOUR SCHEMA> TO _SYS_REPO WITH GRANT OPTION;
If you miss this step, an error will occur when you activate your views later.
Click here to know more about this.

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.

--REPLACE SCHEMA_NAME WITH YOUR SCHEMA
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)
);

To know more about Table Type, check the article SAP HANA Table Type.

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 defines a procedure as read-only. 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
Steps to Create The Procedure:

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;
Refresh the procedure folder under schema in the left Systems tab. You will see the created procedure there.

SAP HANA Procedure


How to call Procedure in HANA:

We call procedure using CALL statement. Execute the below statement to call this procedure.

CALL SCHEMA_NAME."PROCEDURE_SALES_REPORT" (10, null);


SAP HANA Procedure

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.

Continue Reading:
Create Procedure in HANA - Do Not Use SQL Editor Any More




Support us by sharing this article.



Explore More
Close X
Close X

8 thoughts on “SAP HANA Procedure

  1. Ashutosh Hans says:

    How to call a procedure using xsodata.. please help

    • Admin says:

      Hi Ashutosh,
      You can not directly call a procedure from xsodata. You can wither wrap the procedure in a view and pass that in xsodata. Or use XSJS t call procedure.
      For XSJS procedure call check –

      • bella30 says:

        Hi,
        My procedure has a output table parameter represented by the ‘?’ symbol :-
        var query = ‘CALL \”RADMIN\”.\”PROJ_CWIP_R_Int.Procedures::SP2_CWIP_SUM_REPORT\”( 500, “20150401”, 90, ? )’;
        How do i process that table using result set ?
        Does this need to be converted into a JSON string to be consumed in UI5?

        • Admin says:

          Hi Bella,
          You can call procedure in xsjs service. The sample code would be like

          conn = $.db.getConnection();
          query = “{ call \”MYSCHEMA\”.\”Procedure\”( 500, “20150401″, 90, ?) }”;
          pstmt= conn.prepareCall(query);
          pstmt.execute();
          rs = pstmt.getResultSet();
          var resultArray = [];
          while (rs.next()) {
          }

          inside xsjs, you can finally convert the data to JSON and send it to SAPUI5.

  2. Anindita says:

    CALL Schema_Name.”Package_Name/Procedure_Name” throws a invalid name of function or procedure error.
    CALL “_SYS_BIC”.”PackageName/Procedure_Name” throws a insufficient privilege error.
    What is the correct syntax for calling a procedure?

  3. Hari Kumar says:

    Excellent blog

    Question.Can I restrict some one from creating objects under my Schema?

  4. Mudit Bhatnagar says:

    Firstly its a very informative article and thanks for spreading education. I just have a few confusion:

    1. In this article you say that “Procedures can have multiple input and output parameters” but I have not seen any examples of a procedure with multiple output parameters(scalar or table type). Can you confirm if multiple parameters as output is achievable or not.

    2. If i assume that a procedure supports multiple output parameters then how can we retrieve multiple results in a SQL console or may e an xsjs service.

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

Current day month ye@r *

 © 2017 : saphanatutorial.com, All rights reserved.  Privacy Policy