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:
|
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. |
Topic ID: 750074