Skip to Content

Using APPLY FILTER statement

0 %
Using APPLY FILTER statement
// Explore More Tutorials

Using APPLY FILTER statement


Leveraging SQLScript in Stored Procedures & User Defined Functions

You will learn

In contrast to EXEC and EXECUTE IMMEDIATE, APPLY_FILTER is SQL injection save. Furthermore the result of the APPLY_FILTER will be assigned to a table variable. This allows further processing of the result by referring to the table variable. As APPLY_FILTER does not allow executing dynamic DML/DDL statements the procedure can be flagged read only which allows further optimization.

Please note - This tutorial is based on SPS11

Step 1: Add output parameter

Return to the procedure editor. Add an output parameter called ex_user_filtered_products and reference the table as the type.

procedure editor
Step 2: Edit the procedure

Now that we are not using the dynamic SQL keywords, we no longer need a read/write procedure, so add the READS SQL DATA before AS.


Remove the EXECUTE IMMEDIATE statement and instead insert the following SELECT statement and APPLY_FILTER statement using table variable assignments. The APPLY_FILTER needs two input parameters: table variable which will used for filtering and a scalar variable which contains the string.

modify statement
Step 3: Check complete code

The completed code should be very similar to this. If you do not wish to type this code, you can reference the solution web page at http://<hostname>:51013/workshop/admin/ui/exerciseMaster/?workshop=dev602&sub=ex2_18

PROCEDURE "dev602.procedures::get_product_by_filter" (
      IN im_product_filter_string varchar(5000),
      OUT ex_user_filtered_products "" )
--DEFAULT SCHEMA <default_schema_name>

pre_filtered_products =
     SELECT * FROM "" WHERE CATEGORY NOT IN ('Laser printers');

ex_user_filtered_products = APPLY_FILTER(:pre_filtered_products, :im_product_filter_string ) ;

Step 4: Save and build
  1. Click Save.

Use what you have learned already and perform a build on your hdb module. Then return to the HRTT page and invoke the procedure again.

Step 5: Run and check results

Click Format Code.

format code

Enter the filter string for the input parameter as

'CATEGORY = ''Notebooks'''

and click Run.

filter string

Once again, the results are displayed, but this time they are passed through a parameter which you are able to access for further processing.

Step 6: Test different input

Change the input parameter value to ‘OR 1 = 1’ and click “Run” again.

input parameter

You will notice you now get an error message when passing ‘OR 1 = 1’ to the procedure. This happens as the provided string will no longer be concatenated to the predefined filter conditions. Instead the provided string will be treated as a stand-alone filter condition, in this case having an invalid syntax.


Next Steps

Next Steps

Back to top