You will learn

In this exercise, you will learn the differences between dynamic SQL (EXEC, EXECUTE IMMEDIATE) and applying a dynamic filter.
Please note - This tutorial is based on SPS11

Details

Step 1: Create new procedure

Right click on the procedures folder and choose New, then Procedure.

Enter the name of the procedure as get_product_by_filter. Click the drop down box for Schema.

Done

Please log in to access this content.

Step 2: Change namespace

Change the namespace from Undefined to dev602.procedures. Add an input parameter named im_product_filter_string, type varchar with a length of 5000.

Done

Please log in to access this content.

Step 3: Edit procedure

Because dynamic SQL is not supported in “Read-only” procedures, you must remove the "“READS SQL DATA” keywords as shown here.

Done

Please log in to access this content.

Step 4: Insert the EXEC statements

Between the BEGIN and END statements, insert the EXEC statements as shown. The completed code should look 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_17

```
PROCEDURE “dev602.procedures::get_product_by_filter” (
IN im_product_filter_string VARCHAR(5000) )
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
–DEFAULT SCHEMA
AS
BEGIN

EXEC ‘SELECT count(*) FROM “dev602.data::MD.Products” where CATEGORY NOT IN (’‘Laser printers’‘)’
|| :im_product_filter_string ;

END
```

Save the procedure

Done

Please log in to access this content.

Step 5: Build your module

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

A new SQL tab will be opened. Add the filter string as AND CATEGORY = ''Notebooks'''

Click the Run button. You will notice that you get no results from the call at all. Also by using the EXEC statement, there is a possibility of SQL injection