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
Create Procedure in HANA - Do Not Use SQL Editor Any More


In the previous article SAP HANA Procedure - Old Wine in New Bottle we learnt what is Procedure and how to create a procedure using SQL editor.

In this article we will learn a better and simpler way of creating procedure using Modeler Perspective.
Prerequisites:

Before creating a procedure as part of this exercise, we need to create the tables that will be used. Let us do that.
This is similar as we did in our previous article SAP HANA Procedure - Old Wine in New Bottle

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:

  1. Open HANA Studio and expand the SAP HANA system.
  2. 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>;
  3. 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);
  4. 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.

Steps to Create The Procedure:

    1. Right click on the package and go to New -> Procedure.

      SAP HANA Procedure

    2. Specify name and description and click on Finish.

      SAP HANA Procedure

    3. Paste the below code between BEGIN and END.

      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;


    4. On the right side "Output pane" there is "Output Parameters". Right click on that and click on New. Create the output parameter as shown below. Note that this has to be exactly same as last SQL statement of the procedure.

      SAP HANA Procedure

    5. Click on the "Input Parameters" in Input Pane and select "New Scalar Parameter". Create a parameter as DISCOUNT.

      SAP HANA Procedure

    6. Click on Activate button on the top right corner. The procedure will be activated and you can see a successful message in Job Log.

      SAP HANA Procedure

    7. The runtime object of this procedure is created in _SYS_BIC schema. You may check that.

      SAP HANA Procedure

    8. Call the procedure using CALL statement.
      CALL "_SYS_BIC"."sap-hana-tutorial/Procedure_SalesReport" (100, NULL);


      SAP HANA Procedure






Support us by sharing this article.



Explore More
Close X
Close X

20 thoughts on “Create Procedure in HANA

  1. Ashutosh Hans says:

    I am new to SAP Hana please guide me where to write this call procedure statement in any UI5 project ,in the controller.js?
    Thanks in advance for the help.

  2. satish says:

    Hi,

    I was going through sapahanatutorial website for creating procedures in SAP HANA

    1) first I created a procedure using SQL editor by following this link
    http://saphanatutorial.com/sap-hana-procedure/

    2) Next in the below link it says “Create Procedure in HANA – Do Not Use SQL Editor Any More”
    http://saphanatutorial.com/create-procedure-in-hana/

    But I could not find the difference between 1 and 2..

    Can you please assist, if I am missing something to find out the difference in both ways..

    • Admin says:

      Hi Satish,
      The difference is:
      1: In first approach, the design time definition of procedure is not saved. But in second approach, you always have the design time definition saved in Content.
      2. When we need to ship content to customers, we cannot follow first approach. Because it is not advisable to send the code to customer and ask them to execute to create procedure.
      While in 2nd approach, the procedure definition is inside a package. It can be shipped with Delivery Unit and easily imported in any other HANA system.

      Regards,
      Admin

  3. SG says:

    Very helpful info. You’ve made it look encouraging. Many Thanks.

    I tried the last statement where I ‘call’ the procedure, however, I don’t see any records in the result set at all, could you tell me if i’m missing some privileges..?

    • Admin says:

      Hi SG,
      Thanks for the feedback. In case you are missing privileges, you will get a error message.
      However, if you are only getting empty output, this may be because of many reasons. For example:
      1. You may not have data into table.
      2. You may have done wrong join.

      Please crosscheck the above mentioned points.

      Regards,
      Admin

  4. Ken says:

    If you don’t use the create statement how do you control what schema the procedure will get created in? Thanks

    • Shiw says:

      While you create the procedure by right clicking it will ask for schema, check the step 2 mentioned int he blog.

      • Jeeva says:

        The Schema field is greyed out in the wizard to create procedure. So is it like, all the procedures can be created only in _SYS_BIC schema if done through the wizard instead of sql script?

  5. Dileep says:

    Hello,

    Could you help me on below points.

    1) Is it possible to create output scalar, instead of Table?
    2) In SQL editor, while writing we can use “with result view to passing the result in to column view”. Is it possible same by creating procedure under package?

    Regards,
    Dileep.

  6. Hendra says:

    Hi,
    I’d like to ask, if I want to create stored procedure use this mechanism : Modeler Perspective,
    but how to change ‘Access Mode’ to not ‘READ ONLY’
    because I want to make dynamic SQL, this is the sample script

    /********* Begin Procedure Script ************/
    BEGIN
    DECLARE myrowid integer;
    myrowid := 0;
    select count(*) into myrowid from “PUBLIC”.”M_TABLES” where schema_name =:schemaname and table_name=:tablename;

    IF (:myrowid > 0 ) then
    exec ‘DROP TABLE ‘||:schemaname||’.’||:tablename;
    END IF;

    END;
    /********* End Procedure Script ************/

  7. Venkata says:

    Hi,

    As part of the procedure, out put of the first two select statements is assigned to ‘Var1′ and Var2’. What are those variables? Where did we define them?

    Thanks.

  8. chendrasekhar says:

    Hi ,

    its very useful procedure .Thanks.

    colud you please expalin more about ‘Var1′ and Var2′ used in the program .
    Is it required to declare in the program before using it.
    Thanks

  9. Ahmed says:

    Hi I need URGENT HELP,

    I have created the schema, tables and procedure in the same way as given here,
    I have also executed
    GRANT Select ON SCHEMA “AKHAN_SCHEMA” TO _SYS_REPO WITH GRANT OPTION ;

    but when Activating my procedure i am getting this error.
    Activation ID: 8866 (Repository: User is not authorized to execute specified operation;User ‘AKHAN_SCHEMA’ has no authorization, privilege: ‘REPO.MODIFY_CHANGE’!)

  10. Sri says:

    Hi Admin,

    Very usefull document to create basic procedure for biginers… Thanks

  11. YOGESH says:

    Hello i understand you concept i am get confused in

    CALL “_SYS_BIC”.”sap-hana-tutorial/Procedure_SalesReport” (100, NULL);

    What is use of NULL in this Part, where you will Declare this value because Discount value is 100 you in the code but you not declare NULL

    Thank You

    Regards
    B.Yogesh

    • Admin says:

      Hello,

      Send us screenshot on what you were trying to do, where you find difficulty in understanding.
      Screenshots would provide more clarity. Send us at “admin@saphanatutorial.com”

      Thanks,
      Admin

  12. dahmoune says:

    Great job done here, thanks for the step by step tutorial.
    I have a question regarding BW Data base tables (active table of DSO or write-optimized DSO), how can we create a procedure which can read and write data in DSO ?
    how can we find the schema where the dso table are stored in DB

    Thanks for the help

  13. soe says:

    call existstable (‘RowReport’,’EACTRADE’)

    Why isn’t This statement working Command in sap crystal report ?

  14. palani says:

    Thank you very much for the document.

    its very nice, to learn SAP hana – procedure module.

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