Use of Array in Procedure

In this article we will show an example on - How to create and use Array inside procedures.

Introduction:

In SQLScript array can be declared using ARRAY function.
Example 1:
Define an integer array that contains the numbers 1,2 and 3.
            array_id INTEGER ARRAY[] := ARRAY(1, 2, 3);

Example 2:
Define an empty array of type INTEGER.
            array_int INTEGER ARRAY;

UNNEST Function:

The UNNEST function converts an array into a table. UNNEST returns a table including a row for each element of the array specified.
If there are multiple arrays given, the number of rows will be equal to the largest cardinality among the cardinalities of the arrays. In the returned table, the cells that are not corresponding to the elements of the arrays are filled with NULL values.
Note: The UNNEST function cannot be referenced directly in FROM clause of a SELECT statement.

In this example we are going to create 4 arrays and create a table using the arrays. The output will be sent in output using a table type.

Create table type:


CREATE TYPE "<SCHEMA_NAME>".TT_PRODUCT_SALES  AS TABLE(
           "PRODUCTID" VARCHAR(100),
           "CATEGORY" VARCHAR(100),
           "PRICE" DECIMAL(15,2),
           "SALEPRICE" DECIMAL(15,2)
);

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>."PRODUCT_ARRAY"(
            OUT output_table <SCHEMA_NAME>."TT_PRODUCT_SALES" )
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER
    AS
/*********BEGIN PROCEDURE SCRIPT ************/
BEGIN
      DECLARE productid VARCHAR(20) ARRAY;
      DECLARE category VARCHAR(20) ARRAY;
      DECLARE price DECIMAL(15,2) ARRAY;
      DECLARE saleprice DECIMAL(15,2) ARRAY;
   
      productid[1] := 'ProductA';
      productid[2] := 'ProductB';
      productid[3] := 'ProductC';
      category[1] := 'CategoryA';
      category[2] := 'CategoryB';
      category[3] := 'CategoryC';
   
      price[1] := 19.99;
      price[2] := 29.99;
      price[3] := 39.99;
                 
      saleprice[1] := 15.99;
      saleprice[2] := 25.99;
      saleprice[3] := 35.99;
   
      output_table = UNNEST(:productId, :category, :price, :saleprice)
        AS ("PRODUCTID", "CATEGORY", "PRICE", "SALEPRICE");
END;

Call procedure:

Call the procedure using below statement.
CALL <SCHEMA_NAME>."PRODUCT_ARRAY"(?)

Click here to go to Next Chapter. 4.8  Dynamic Filter