# 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, CFELSE, CFELSEIF tags to control how the SQL statement is constructed, for example:

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

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

    </CFQUERY>

First, you need to create an input form, which asks for information about several fields in the Employees table. Instead of entering information in each field, a user may want to search on certain fields, or even on only one field. To search for data based on only the fields the user enters in the form, you use CFIF statements in the SQL statement.

| To create the input form:

1. Create a new application page in Studio.
2. Enter the following code:

       <HTML>
       <HEAD>
       <TITLE>Input form</TITLE>
       </HEAD>
       <BODY>
       <!--- Query the Employees table to be able to populate the form --->
       <CFQUERY NAME="AskEmployees" DATASOURCE="CompanyInfo">
       SELECT
           FirstName,
           LastName,
           Salary,
           Contract
       FROM         Employees
       </CFQUERY>

       <!--- define the action page in the form tag. The form variables will
       pass to this page when the form is submitted --->

       <FORM ACTION="getemp.cfm" METHOD="post">

       <!-- text box -->
       <P>
       First Name: <INPUT TYPE="Text" NAME="FirstName" SIZE="20"
       MAXLENGTH="35"><BR>
       Last Name: <INPUT TYPE="Text" NAME="LastName" SIZE="20"
       MAXLENGTH="35"><BR>
       Salary: <INPUT TYPE="Text" NAME="Salary" SIZE="10" MAXLENGTH="10">
       </P>

       <!-- check box -->
       <P>
       Contractor? <input type="checkbox" name="Contract" value="Yes" >Yes
       if checked
       </P>

       <!-- reset button -->
       <INPUT TYPE="reset" NAME="ResetForm" VALUE="Clear Form">

       <!-- submit button -->
       <INPUT TYPE="submit" NAME="SubmitForm" VALUE="Submit">
       </FORM>
       </BODY>
       </HTML>

3. Save the page as `askemp.cfm`.

Once you have created the input form, you can then create the action page to process the user's request. This action page will determine where the user has entered search criteria and search based only on those criteria.

| To create the action page:

1. Create a new application page in Studio.
2. Enter the following code:

       <HTML>
       <HEAD>
           <TITLE>Get Employee Data</TITLE>
       </HEAD>

       <BODY>
       <CFQUERY NAME="GetEmployees" DATASOURCE="CompanyInfo">
           SELECT *
       FROM Employees
       WHERE 0=0

       <CFIF #Form.FirstName# is not "">
       AND Employees.FirstName LIKE '#form.FirstName#%'
       </CFIF>

       <CFIF #Form.LastName# is not "">
       AND Employees.LastName LIKE '#form.LastName#%'
       </CFIF>

       <CFIF #Form.Salary# is not "">
       AND Employees.Salary >= #form.Salary#
       </CFIF>

       <CFIF isDefined("Form.Contract") IS "YES">
       AND Employees.Contract = 'Yes'
       <CFELSE>
       AND Employees.Contract = 'No'
       </CFIF>

       </CFQUERY>

       <H3>Employee Data Based on Criteria from Form</H3>
       <TABLE>
       <TR>
           <TH>First Name</TH>
           <TH>Last Name</TH>
           <TH>Salary</TH>
           <TH>Contractor</TH>
       </TR>
       <CFOUTPUT QUERY="GetEmployees">
       <TR>
           <TD>#FirstName#</TD>
           <TD>#LastName#</TD>
           <TD>#DollarFormat(Salary)#</TD>
           <TD>#Contract#</TD>
       </TR>
       </CFOUTPUT>
       </TABLE>

       </BODY>
       </HTML>

3. Save the page as `getemp.cfm`.
4. Open the file `askemp.cfm` in your browser and enter criteria into any fields, then submit the form.
5. The results should meet the criteria you specify.

### Code Review

The action page `getemp.cfm` build a SQL statement dynamically based on what the user enters in the form page AskEmp.cfm.

CFML Code | Description
--- | ---
    SELECT *
        FROM Employees
        WHERE 0=0
| Get all the records from the Employees table as long as 0=0. 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.
    <CFIF #Form.FirstName# is not "">
        AND Employees.FirstName LIKE
    '#form.FirstName#%'
    </CFIF>
| If the user entered anything in the FirstName text box in the form, add "AND Employees.FirstName LIKE `[what the user entered in the FirstName text box]%'" to the SQL statement.
    <CFIF #Form.LastName# is not "">
        AND Employees.LastName LIKE
    '#form.LastName#%'
    </CFIF>
| If the user entered anything in the LastName text box in the form, add "AND Employees.LastName LIKE `[what the user entered in the LastName text box]%'" to the SQL statement.
    <CFIF #Form.Salary# is not "">
        AND Employees.Salary >=
    #form.Salary#
    </CFIF>
| If the user entered anything in the Salary text box in the form, add "AND Employees.Salary >= [what the user entered in the Salary text box]" to the SQL statement.
    <CFIF isDefined("Form.Contract") IS
    "YES">
        AND Employees.Contract = 'Yes'
        <CFELSE>
        AND Employees.Contract = 'No'
    </CFIF>
| If the user checked the Contractor check box, get data for the employees who are contractors, otherwise, get data for employees who are not contractors.