SQL Statements Constructed at Run Time

Applications that perform ad hoc analysis commonly build SQL statements at run time. For example, a spreadsheet might allow a user to select columns from which to retrieve data:

SQLCHAR   *Statement, *TableName;
SQLCHAR   **TableNamesArray, **ColumnNamesArray;
BOOL    *ColumnSelectedArray;
BOOL    CommaNeeded;
SQLSMALLINT i, NumColumns;

// Use SQLTables to build a list of tables (TableNamesArray[]). Let the user select a
// table and store the selected table in TableName.

// Use SQLColumns to build a list of the columns in the selected table
// (ColumnNamesArray). Set NumColumns to the number of columns in the table. Let the
// user select one or more columns and flag these columns in ColumnSelectedArray[].

// Build a SELECT statement from the selected columns.
CommaNeeded = FALSE;
strcpy(Statement, "SELECT ");
for (i = 0; i = NumColumns; i++) {
 if (ColumnSelectedArray[i]) {
  if (CommaNeeded) 
    strcat(Statement, ",");
  else 
    CommaNeeded = TRUE;
  strcat(Statement, ColumnNamesArray[i]);
 }
}

strcat(Statement, " FROM ");
strcat(Statement, TableName);

// Execute the statement directly. Because it will be executed only once, do not
// prepare it.
SQLExecDirect(hstmt, Statement, SQL_NTS);

Another class of applications that commonly constructs SQL statements at run time are application development environments. However, the statements they construct are hard-coded in the application they are building, where they can usually be optimized and tested.

Applications that construct SQL statements at run time can provide tremendous flexibility to the user. As can be seen from the preceding example, which did not even support such common operations as WHERE clauses, ORDER BY clauses, or joins, constructing SQL statements at run time is vastly more complex than hard-coding statements. Furthermore, testing such applications is problematic, because they can construct an arbitrary number of SQL statements.

A potential disadvantage of constructing SQL statements at run time is that it takes far more time to construct a statement than use a hard-coded statement. Fortunately, this is rarely a concern. Such applications tend to be user-interface intensive, and the time the application spends constructing SQL statements is generally small compared to the time the user spends entering criteria.