Skip to Content
Previous

Using Dynamic SQL vs Dynamic Filtering

By Craig Cmehil

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

Details


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

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

modify
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

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

HRTT

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
Please log in to access this content.

Next Steps

Updated 03/24/2017

Time to Complete

10 Min.

Intermediate

Prerequisites

Next
Back to top