Dynamic Filter in HANA

In this article we will show an example on - How to implement and call dynamic filtering in procedure.

Introduction:

SAP does not recommend the use of dynamic SQL (EXEC statement) when developing SQLScript procedures.
For dynamic SQL, we now have a new statement in SQLScript called APPLY_FILTER.
This statement accepts two parameters.

• The first parameter is the dataset in which you want to apply the filter.  This dataset can be a database table, database view, HANA attribute or calculation view, or even an intermediate table variable. 
• The second parameter is the filter condition itself. This would be very similar syntax that you would use in the WHERE clause of a SELECT statement.  

In this example we are going to use table PRODUCT and pass the filter condition dynamically while calling the procedure. The output will be sent in output using a table type.

Create Table:

Copy and paste the below script in SQL editor and execute.
Note: If you already have created the PRODUCT table in previous example, then skip this step.

----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 table type:

CREATE TYPE <SCHEMA_NAME>.TT_PRODUCT AS TABLE(
      "PRODUCT_ID" VARCHAR(10),
      "PRODUCT_NAME" VARCHAR (100),
      "CATEGORY" VARCHAR (100)
);

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_DYNAMIC_FILTER"(
        IN im_filter_string VARCHAR(5000),
        OUT output_table <SCHEMA_NAME>."TT_PRODUCT" )
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER
    AS
/*********BEGIN PROCEDURE SCRIPT ************/
BEGIN
    output_table = APPLY_FILTER("<SCHEMA_NAME>"."PRODUCT", :im_filter_string) ;
END;

Call procedure:

Call the procedure using below statement.

CALL <SCHEMA_NAME>."PROCEDURE_DYNAMIC_FILTER"(im_filter_string => '"CATEGORY" = ''Clothes''', output_table => ?);

Click here to go to Next Chapter.  4.9 Exception Handling