SAP HANA Database SQLScript APPLY_FILTER SQL Command

APPLY_FILTER enables use of dynamic SQL on SAP HANA database by SQLScript developers. Dynamic SQL enables SAP HANA SQL programmers to build SQL statements dynamically before execution. Although programmers should avoid constructing and executing dynamic SQL statements (especially for EXEC and EXECUTE IMMEDIATE) wherever possible because of security concerns and SQL injection possibilities, in some cases SQL database developers prefer using dynamic SQL for fast and flexiable solutions. Additionally, SQLScript APPLY_FILTER command is considered as SQL injection safe.

This SAP HANA SQLScript tutorial shows how to use apply_filter SQL command on HANA database developments with samples.

SAP HANA database provides Exec, Execute Immediate and Apply_Filter commands for SQL developers to enable dynamic SQL.

On my hanatrial account, I have a demo table "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::MD.Products"
Although this naming is very hard to read and use, it is a chance that developers can creaate public synonyms for the table object on a SAP HANA database.
Your SQL developer can also create public synonym for the demo table including product definitions and name it PRODUCTS to use later in this SQLScript tutorial.

HANA database Apply_Filter SQL command

Let's assume your HANA database developer is executing a SELECT statement with WHERE clause as follows:


select * from Products where ProductId = 'HT-1001';

Code

It is possible to provide the WHERE clause criteria ProductId = 'HT-1001' using apply_filter with a filter condition variable as follows:


DO BEGIN

declare filtercondition varchar(50) := ' ProductId = ''HT-1001'' ';

products = APPLY_FILTER(Products, :filtercondition);

select * from :products;

END;

Code

SQLScript apply_filter statement takes two parameters.

The first argument shows the dataset on which the filter criteria will be applied.
This is the data source.
SQL developers can provide database tables or database views as well as intermediate table variables as an parameter argument to apply_filter dataset parameter.

Second apply_filter argument is the filter condition.
SQL developers can pass filtering criteria into Apply_Filter SQL command.
Apply_Filter criteria parameter is just like using a WHERE condition on the SELECT statement over datasource object.

The output of the apply_filter SQL command is an other dataset which can be assigned to a table variable.

Addition to the first example, the source dataset for apply_filter command can also be used dynamically with the help of a variable as follows;


DO BEGIN

declare filtercondition varchar(50) := ' ProductId = ''HT-1001'' ';



lt_product = SELECT * FROM Products;

lt_results = APPLY_FILTER(:lt_product, :filtercondition);

select * from :lt_results;

END;

Code

Let's now examine the use of Apply_Filter in a SAP HANA database stored procedure created for demo purpose on cloud.

Following HANA database stored procedure leverages the concept of dynamic filters. It reads data from the “Products” table and applies a filter which is passed as an input parameter to the procedure. The result set then shows the filtered dataset using the APPLY_FILTER statement

Apply_Filter SQLScript command with sample SAP HANA database stored procedure


CREATE PROCEDURE SAP_HANA_DEMO."sap.hana.democontent.epm.Procedures::get_products_by_filter" (

 IN im_filter_string VARCHAR(5000),

 out ex_products "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::MD.Products")

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

READS SQL DATA AS

BEGIN



ex_products = APPLY_FILTER("SAP_HANA_DEMO"."sap.hana.democontent.epm.data::MD.Products", :im_filter_string) ;



END;

Code

HANA database programmers can execute above stored procedure as seen in following sample


do begin



declare filter_criteria varchar(400) := 'category = ''Notebooks''';



CALL "SAP_HANA_DEMO"."sap.hana.democontent.epm.Procedures::get_products_by_filter"(

 IM_FILTER_STRING => filter_criteria,

 EX_PRODUCTS => lt_products

);



select * from :lt_products;



end;

Code

call stored procedure in SAP HANA database using dynamic query

An other example where APPLY_FILTER SQL command is used in a HANA database procedure is as following. In this example, SQL programmer can create a table data type and use as the stored procedure input parameter data type.

Let's create the table type first. Execute following "CREATE TYPE" SQLScript command.


-- table data type creation

CREATE TYPE procedures_tblty

AS

TABLE (

 SCHEMA_NAME NVARCHAR(256),

 PROCEDURE_NAME NVARCHAR(256),

 OWNER_NAME NVARCHAR(256)

);

Code

After execution, under the schema node, under Procedures > Table Types SQL developers can view the database object.

SAP HANA database table type object

Now we can create the stored procedure on HANA database using the following CREATE PROCEDURE script


CREATE PROCEDURE FILTER_PROCEDURES (

 IN filter_criteria nvarchar(100),

 OUT procedures procedures_tblty

)

AS

BEGIN



filter_result = APPLY_FILTER(SYS.PROCEDURES, :filter_criteria);

procedures = SELECT schema_name, procedure_name, owner_name FROM :filter_result;



END;

Code

SQL developer is now ready to execute the HANA database stored procedure using CALL command.
Here is an example.


CALL FILTER_PROCEDURES(' UPPER(PROCEDURE_NAME) like ''%PROCEDURE%''', ?);

Code

Please note that the output parameter which is the returning result set is not defined here. I used "?" question mark, so the CALL command returns the output of the procedure.

Apply_Filter on SAP HANA database table using stored procedure

Or SQL programmer can pass the second argument (lt_tbl) which is for the result set as in below sample SQLScript


do begin



declare lt_tbl procedures_tblty;



--CALL FILTER_PROCEDURES(' UPPER(PROCEDURE_NAME) like ''%PROCEDURE%''', ?);

CALL FILTER_PROCEDURES(' UPPER(PROCEDURE_NAME) like ''%PROCEDURE%''', lt_tbl);

select * from :lt_tbl;



end;

Code