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
How to Use Table Type in SAP HANA Procedure


In this article we will show an example of SAP HANA procedure to show – How to use table type inside procedure to send output data.

In this example we are going to use 2 tables PRODUCTS and SALES. The procedure will join these 2 tables to get PRODUCT_NAME and SALES_AMOUNT. The output will be sent in output using a table type.

Create tables and table type in SAP HANA:

Copy and paste the below script in SQL editor and execute.

----REPLACE <SCHEMA_NAME> WITH YOUR SCHEMA NAME
---- Create Product table
create column table "<SCHEMA_NAME>"."PRODUCT"(
      "PRODUCT_ID" VARCHAR(10),
      "PRODUCT_NAME" VARCHAR (100),
    "CATEGORY" VARCHAR(100),
      primary key ("PRODUCT_ID")
);

insert into "<SCHEMA_NAME>"."PRODUCT" values('P1','Shirts', 'Clothes');
insert into "<SCHEMA_NAME>"."PRODUCT" values('P2','Jackets', 'Clothes');
insert into "<SCHEMA_NAME>"."PRODUCT" values('P3','Trousers', 'Clothes');
insert into "<SCHEMA_NAME>"."PRODUCT" values('P4','Coats', 'Clothes');
insert into "<SCHEMA_NAME>"."PRODUCT" values('P5','Purse', 'Accessories');


-- Create Sales table
create column table "<SCHEMA_NAME>"."SALES"(
      "PRODUCT_ID" VARCHAR(10),
      "SALES_AMOUNT" DOUBLE,      
      PRIMARY KEY ("PRODUCT_ID", "SALES_AMOUNT") );

insert into "<SCHEMA_NAME>"."SALES" values('P1',100);
insert into "<SCHEMA_NAME>"."SALES" values('P2',90);
insert into "<SCHEMA_NAME>"."SALES" values('P5',85);
insert into "<SCHEMA_NAME>"."SALES" values('P2',80);
insert into "<SCHEMA_NAME>"."SALES" values('P1',75);
insert into "<SCHEMA_NAME>"."SALES" values('P3',85);
insert into "<SCHEMA_NAME>"."SALES" values('P4',75);
insert into "<SCHEMA_NAME>"."SALES" values('P1',65);
insert into "<SCHEMA_NAME>"."SALES" values('P2',65);

--Create table type
CREATE TYPE "<SCHEMA_NAME>".TT_SALES AS TABLE(
"PRODUCT_NAME" VARCHAR (100),
"SALES_AMOUNT" DOUBLE
);

To know more about table type, read SAP HANA Table Type.

Create procedure:

Copy and paste the below script to create the procedure.

----REPLACE <SCHEMA_NAME> WITH YOUR SCHEMA NAME
CREATE PROCEDURE  <SCHEMA_NAME>."PROCEDURE_SALES_REPORT"(
            OUT OUTPUT_TABLE  <SCHEMA_NAME>."TT_SALES" )
           LANGUAGE SQLSCRIPT
           SQL SECURITY INVOKER
           AS
/*********BEGIN PROCEDURE SCRIPT ************/
BEGIN

OUTPUT_TABLE = SELECT T1.PRODUCT_NAME, SUM(T2.SALES_AMOUNT) as SALES_AMOUNT
            FROM  <SCHEMA_NAME>.PRODUCT AS T1
            INNER JOIN
            <SCHEMA_NAME>.SALES AS T2
            ON T1.PRODUCT_ID = T2.PRODUCT_ID
            GROUP BY T1.PRODUCT_NAME;

END;

Call procedure:

Call the procedure using below statement and see the output.
CALL <SCHEMA_NAME>."PROCEDURE_SALES_REPORT"(?);

SAP HANA Procedure Example

Continue reading:
Check the next example on Local Scalar Variable in Procedure




Support us by sharing this article.



Explore More
Close X
Close X

3 thoughts on “SAP HANA Procedure Example – How to Use Table Type

  1. Lajwanth says:

    Excellent work buddy…
    i was looking in to sap hana sql script guidelines.. but this explanation gives more glance on the procedures…

  2. Vijay says:

    Excellent source for interviews and practice. Great help. 80% of the questions were
    asked from here in my previous interview.

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