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"(?);

Click here to go to Next Chapter. 4.3  Local Scalar Variable