BackUp LevelNext

Dynamic SQL

Embedding SQL queries that use dynamic parameters is a powerful mechanism for linking variable inputs to database queries. However, in more sophisticated applications, you will often want user inputs to determine not only the content of queries but also the structure of queries.

Dynamic SQL allows you to dynamically determine (based on runtime parameters) which parts of a SQL statement are sent to the database. So if a user leaves a search field empty, for example, you could simply omit the part of the WHERE clause that refers to that field. Or, if a user does not specify a sort order, the entire ORDER BY clause could be omitted.

Dynamic SQL is implemented in ColdFusion by using CFIF/CFELSEIF/CFELSE tags to control how the SQL statement is constructed.

Syntax

A dynamic SQL statement using the CFQUERY tag takes the following form:

<CFQUERY NAME="queryname"
    DATASOURCE="datasourcename">
...Base SQL statement

<CFIF value operator value >
...additional SQL
</CFIF>

</CFQUERY>

Example: Using CFIF

For example, in the following code, a series of CFIF tags determine which SQL statements to append to the base SQL SELECT statement:

<CFQUERY NAME="GetParkList"
    DATASOURCE="CF 4.0 Examples">
    SELECT * 
        FROM Parks
        WHERE 0=0

<CFIF #ParkName# is not "">
    AND ParkName LIKE '%#ParkName#%'
</CFIF>

<CFIF #ParkType# is not "AllTypes">
    AND ParkType = '#ParkType#'
</CFIF>

<CFIF #Region# is not "AllRegions">
    AND Region = '#Region#'
</CFIF>

<CFIF #State# is not "">
    AND State = '#State#'
</CFIF>

</CFQUERY>

Tip

The WHERE 0=0 clause has no impact on the query submitted to the database. But if none of the conditions is true, it ensures that the WHERE clause does not result in a SQL syntax error.

Example: Creating a select list

This example shows how to use dynamic SQL to ensure that a multiple select list is created.

<CFQUERY NAME="GetParkList"
DATASOURCE="CF 4.0 Examples">
    SELECT * 
        FROM Parks
        WHERE 0=0
<CFIF IsDefined("ParkName_ID")>
    AND ParkName_ID IN (#Form.ParkName_ID#)
</CFIF>
</CFQUERY>

BackUp LevelNext

allaire

AllaireDoc@allaire.com
Copyright © 1998, Allaire Corporation. All rights reserved.