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.

Details


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

    new procedure

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

    procedure name

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

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

    modify

  5. 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 <default_schema_name>
       AS
    BEGIN
    EXEC 'SELECT count(*) FROM "dev602.data::MD.Products" where CATEGORY NOT IN (''Laser printers'')' 
      || :im_product_filter_string  ;
    END
    
  6. Save the procedure

    save procedure

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

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

    new sql tab

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

Updated 09/19/2016

Time to Complete

10 Min.

Intermediate

Prerequisites

Next
Back to top