Table Types in SAP HANA

Introduction to Table Type:

v  A table type is Similar to a database table but do not have an instance

v  Used to define parameters for a procedure that represent tabular results.

In HANA, with the help of SQLScript, we can create a Table Type.

How to Create a Table Type:

A table type is created using statement CREATE TYPE and can be deleted using statement DROP TYPE.

Syntax:

CREATE TYPE [schema.]name AS TABLE

          (name1 type1 [, name2 type2,...])

DROP TYPE [schema.]name [CASCADE]

Example:

Code:

To Create the Type:

create type "KABIL_PRACTICE"."TT_PROFIT" as Table

(

"PRODUCTCODE" nvarchar(15),

"PRODUCTNAME" nVarchar (70),

"BUYPRICE" decimal(34,2),

"MSRP" decimal(34,2),

"PROFIT" decimal(34,2)

);

To Drop the Type:

drop type "KABIL_PRACTICE"."TT_PROFIT";

To Create the Procedure:

create procedure "KABIL_PRACTICE"."TT_PROCEDURE" ( OUT "OUTPUT_TABLE" "KABIL_PRACTICE"."TT_PROFIT" )

as begin

"OUTPUT_TABLE" = Select "PRODUCTCODE","PRODUCTNAME","BUYPRICE","MSRP",("MSRP"-"BUYPRICE") as "PROFIT"

from "KABIL_PRACTICE"."PRODUCTS_SAP_STUDENT";

END;

To Call the Procedure:

call "KABIL_PRACTICE"."TT_PROCEDURE"(?);

Result: