Return to the procedure editor. Add an output parameter called
ex_user_filtered_products and reference the
dev602.data::MD.Products table as the type.
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.
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 ```
PROCEDURE “dev602.procedures::get_product_by_filter” (
IN im_product_filter_string varchar(5000),
OUT ex_user_filtered_products “dev602.data::MD.Products” )
SQL SECURITY INVOKER
READS SQL DATA AS
SELECT * FROM “dev602.data::MD.Products” WHERE CATEGORY NOT IN (‘Laser printers’);
ex_user_filtered_products = APPLY_FILTER(:pre_filtered_products, :im_product_filter_string ) ;
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.
Click “Format Code”.
Enter the filter string for the input parameter as
'CATEGORY = ''Notebooks''' and click “Run”.
Once again, the results are displayed, but this time they are passed through a parameter which you are able to access for further processing.
Change the input parameter value to ‘OR 1 = 1’ and click “Run” again.
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.