Skip to Content

Using Dynamic SQL vs Dynamic Filtering

0 %
Using Dynamic SQL vs Dynamic Filtering

Using Dynamic SQL vs Dynamic Filtering

Leveraging SQLScript in Stored Procedures & User Defined Functions

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


Step 1: Create new procedure

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

new procedure

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

procedure name
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.

change namespace
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.

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) )

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


Save the procedure

save procedure
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'''

new sql tab

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

run procedure

Next Steps

Next Steps

Back to top