Using Dynamic SQL vs Dynamic Filtering

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.

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

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

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

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