dynamic_SQL

  

Virtual Machine  

Dynamic SQL

Purpose

Use Dynamic SQL to build SQL statements dynamically at runtime.

It dynamically fetches the values (table name and criteria) from the variables that are declared in PROIV logic and formulates the query.

For example, dynamic SQL helps you to build a SQL query that operates on a table where the table name and the criteria is unknown until runtime.

The WHERE keyword (not including the keyword WHERE itself) can be stored in one or more PROIV variables and the SQL query is built at execution time.

This greatly enhances the flexibility of the SQL application.

Syntax

SQL DYNAMIC

        SQL-statement

ENDSQL

Syntax Elements

SQL-statement is any SQL statement. This is free-format except that quoted text may not span logic lines.

Use dynamic SQL where:

  • Static SQL does not support the operation you want to perform.

  • SQL statements may depend on user input at runtime.

  • A complex query is executed with a user-defined input.

  • It reduces highly complicated code to a relatively small amount of code.

Remarks

When expanding the SQL statement, PROIV may insert a space before and/or after every string (or variable) it expands. This ensures that the expanded SQL string does not generate an error due to concatenation.

Conversely, do not rely on the lack of spaces between components as written to concatenate strings, or to insert a space at a particular point. Always, craft the SQL statement so that the expanded variables will provide the correct quotes and spaces irrespective of space insertion.

Note: Any other syntax, such as attempts at using PROIV variables for other parts of the SQL statement, or trying to "nest" PROIV variables and expecting multi-pass parsing at expansion time, is unsupported and will lead to undefined results.

Examples

EXAMPLE 1 (NUMERIC)

#POSTCODE = 709

$SQLCONDITION = "POSTCODE = "  + CONV(#POSTCODE)

SQL DYNAMIC

   SELECT CUSTNAME FROM CUSTTABLE WHERE :$SQLCONDITION

ENDSQL

At runtime, the $SQLCONDITION is replaced with the required value and SQL statement is constructed as "SELECT CUSTNAME FROM CUSTTABLE WHERE POSTCODE = 709"

The CONDITION which is stored in a scratch variable is passes with the  select statement inside SQL Dynamic..ENDSQL with WHERE keyword unlike Static values which are used in SQL..ENDSQL.

Example 2

In this case, WHERE keyword is not included in the SQL, instead a condition is passed to a scratch variable. SQL DYNAMIC ... ENDSQL automatically adds WHERE keyword at runtime.

$CUSTCODE = 'A010'

$SQLCONDITION = "CUSTCODE = '"  + $CUSTCODE + "'"

SQL DYNAMIC

   SELECT CUSTNAME FROM CUSTTABLE :$SQLCONDITION

ENDSQL

Example 3

$$SEL = "DO_CSUTOMER_CODE LIKE 'B%'"

IF $STATUS # ' '

      $$SEL = $$SEL + ' AND DO_CUS_STATUS_COD = ' + $STATUS

ENDIF

IF #CRED_LOW > 0

   IF #CRED_HIGH > 0

      $$SEL = $$SEL + ' AND DO_CUS_CREDIT_LIMIT BETWEEN ' + CONV(#CRED_LOW) + ' AND ' + CONV(#CRED_HIGH)

   ELSE

      $$SEL = $$SEL + ' AND DO_CUS_CREDIT_LIMIT >= ' + CONV(#CRED_LOW)

ELSE

   IF #CRED_HIGH > 0

       $$SEL = $$SEL + ' AND DO_CUS_CREDIT_LIMIT <= ' + CONV(#CREDIT_HIGH)

   ENDIF

   SQL DYNAMIC

       SELECT FROM DO_CUST WHERE :$$SEL

   ENDSQL

It is also possible to change the column(s) used in the WHERE clause:

SQL DYNAMIC

     SELECT FROM CUST.PRO WHERE :$FIELD :$CONDITION :$VALUE

ENDSQL

where the PROIV variables $FIELD, $CONDITION, and $VALUE have been set earlier.

Unsupported formats

The following examples are not supported:

$VALUE = ":$ID"

SQL DYNAMIC

  SELECT FROM Employee WHERE ID = :$VALUE

ENDSQL

-- Trying to use Dynamic SQL to lead into Full Function SQL. Not Permitted.

SQL DYNAMIC

  $$SQLStmt

ENDSQL

-- Only the WHERE clause can reside in a variable.

SQL DYNAMIC

  SELECT FROM :$TABLE WHERE EmpID = 1

ENDSQL

-- The table name cannot be a variable.

$EMPID = 'ABC123'

$EMPVAL = ":$EMPID"

SQL DYNAMIC

  SELECT FROM Employee WHERE EmpID = :$EMPVAL

ENDSQL

-- "Nesting" of values not permitted.

Comment on this topic

Topic ID: 750074