Dynamic SQL Command

Dynamic SQL enables execution of an arbitrary SQL command in a virtual procedure. Dynamic SQL is useful when the exact command form is not known before execution.

Usage

SQL

EXECUTE IMMEDIATE <expression>
    AS <variable> <type> [, <variable> <type>]*
    [INTO <variable>]
    [USING <variable>=<expression> [, <variable>=<expression>]*]
    [UPDATE <literal>]

Syntax

<expression> may be of data type STRING or CLOB. The length of strings is not affected by the string length limit set via setMaxStringLength(INTEGER), only by the amount of free memory available;
The optional AS clause is used to define the projected symbol names and types returned by the executed SQL string. The AS clause symbols will be matched positionally with the symbols returned by the executed SQL string. Non-convertible types or too few columns returned by the executed SQL string will result in an error. If the AS clause is not specified, no error will be thrown, but also no results will be returned;
The optional INTO clause will project the dynamic SQL into the specified temporary table. With the INTO clause specified, the dynamic command will execute a statement that behaves like an INSERT with a QUERY EXPRESSION. If the dynamic SQL command creates a temporary table with the INTO clause, then the AS clause is required to define the table’s metadata;
The USING clause allows the dynamic SQL string to contain variable references that are bound at runtime to specified values. This allows for some independence of the SQL string from the surrounding procedure variable names and input names. In the dynamic command USING clause, each variable is only specified by a short name. However, in the dynamic SQL, the USING variable must be fully qualified to DVAR.. The USING clause is only for values used in the dynamic SQL as legal expressions. It is not possible to use the USING clause to replace table names, keywords, etc. This makes using symbols equivalent in power to normal bind expressions in prepared statements. The USING clause helps to reduce the amount of string manipulation needed. If a reference is made to a USING symbol in the SQL string that is not bound to a value in the USING clause, an exception will occur;
The UPDATE clause is used to specify the updating model count. Accepted values are (0, 1, *). 0 is the default value if the clause is not specified.

Examples

Dynamic SQL

Here is an example showing a more complex approach to building criteria for the dynamic SQL string. In short, the virtual procedure AccountAccess.GetAccounts has inputs ID, LastName, and bday. If a value is specified for ID, it will be the only value used in the dynamic SQL criteria. Otherwise, if a value is specified for LastName, the procedure will detect if the value is a search string. If bday is specified in addition to LastName, it will be used to form compound criteria with LastName.

Typically, complex criteria would be formed based upon inputs to the procedure. In this simple example, the criteria is referencing the using clause to isolate the SQL string from referencing a value from the procedure directly:

SQL

BEGIN
...
 
DECLARE string criteria = 'Customer.Accounts.Last = DVARS.LastName';
 
DECLARE string sql_string = 'SELECT ID, First || '' '' || Last AS Name, Birthdate FROM Customer.Accounts WHERE ' || criteria;
 
EXECUTE IMMEDIATE sql_string AS ID integer, Name string, Birthdate date INTO #temp USING LastName='some name';
 
LOOP ON (SELECT ID FROM #temp) AS myCursor
...
 
END

The execution of the SQL string creates the #temp table with the columns ID, Name, Birthdate. Please note that we also have the USING clause to bind a value to LastName, which is referenced in the criteria. After this, the temp table can be used with the values from the Dynamic SQL.

Dynamic SQL with USING Clause and Dynamically Built Criteria String

SQL

BEGIN
...
 
DECLARE string crit = null;
 
IF (AccountAccess.GetAccounts.ID IS NOT NULL)
    crit = '(Customer.Accounts.ID = DVARS.ID)';
    ELSE IF (AccountAccess.GetAccounts.LastName IS NOT NULL)
        BEGIN
            IF (AccountAccess.GetAccounts.LastName == '%')
                ERROR "Last name cannot be %";
            ELSE IF (LOCATE('%', AccountAccess.GetAccounts.LastName) < 0)
                crit = '(Customer.Accounts.Last = DVARS.LastName)';
            ELSE
                crit = '(Customer.Accounts.Last LIKE DVARS.LastName)';
                    IF (AccountAccess.GetAccounts.bday IS NOT NULL)
                        crit = '(' || crit || ' and (Customer.Accounts.Birthdate = DVARS.BirthDay))';
        END
ELSE
    ERROR "ID or LastName must be specified.";
 
EXECUTE IMMEDIATE 'SELECT ID, First || '' '' || Last AS Name, Birthdate FROM Customer.Accounts WHERE ' || crit
    USING ID=AccountAccess.GetAccounts.ID, LastName=AccountAccess.GetAccounts.LastName, BirthDay=AccountAccess.GetAccounts.Bday;
 
...
END

Known Limitations and Workarounds

The use of dynamic SQL command has some limitations - in this section, we will describe them and offer solutions.

1. The use of dynamic SQL command results in an assignment statement requiring the use of a temp table:

SQL

BEGIN
    EXECUTE IMMEDIATE <expression> AS x string INTO #temp;
    DECLARE string VARIABLES.RESULT = (SELECT x FROM #temp);
END

2. The construction of appropriate criteria will be cumbersome if parts of the criteria are not present. For example, if "criteria" were already NULL, the following example results in "criteria" remaining NULL.

SQL

BEGIN
    ...
    criteria = '(' || criteria || ' and (Customer.Accounts.Birthdate = DVARS.BirthDay))';
    ...
END

We recommend ensuring the criteria is not NULL before its usage as a preferred approach. If this is not possible, a good approach is to specify the default as shown in the following example:

SQL

BEGIN
    ...
    criteria = '(' || nvl(criteria, '(1 = 1)') || ' and (Customer.Accounts.Birthdate = DVARS.BirthDay))';
    ...
END

3. If the dynamic SQL is an UPDATE, DELETE, or INSERT command, and you need to specify the AS clause (which would be the case if the number of rows affected needs to be retrieved), you will still need to provide a name and type for the return column if the into clause is specified. ResultSet columns generated from the "AS" clause will then have a default set of properties for length, precision, etc.

SQL

EXECUTE IMMEDIATE <expression> AS x integer INTO #temp;

Please note that this name does not need to match the expected update command symbol "count".