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 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 can simply omit the part of the WHERE clause that refers to that field. Or, if a user does not specify a sort order, you can omit the entire ORDER BY clause.

Implementing dynamic SQL

You implement dynamic SQL 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(s)



<cfif value operator value >

...additional SQL statement(s)

</cfif>



</cfquery>

The following code creates an application that lets a user search the CompanyInfo database for employees by first name, last name, minimum salary, contract status, or any combination of these criteria.

Creating the input form

First, you need to create an input form, which asks for information about several fields in the Employee table. 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:

Create a new application page in ColdFusion Studio.

Enter the following code:

<html>

<head>

<title>Input form</title>

</head>

<body>



<!--- Query the Employee table to be able to populate the form --->

<cfquery name="AskEmployees" datasource="CompanyInfo">

SELECT      

  FirstName, 

  LastName, 

  Salary, 

  Contract

FROM Employee

</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>

Save the page as askemp.cfm.

Creating the action page

After you create the input form, you can create the action page to process the user's request. This action page determines where the user entered search criteria and searches based only on those criteria.

To create the action page:

Create a new application page in ColdFusion Studio.

Enter the following code:

<html>

<head>

  <title>Get Employee Data</title>

</head>



<body>

<cfquery name="GetEmployees" datasource="CompanyInfo">

  SELECT * 

  FROM Employee

  WHERE 



<cfif #form.firstname# is not "">

  Employee.FirstName LIKE '#form.FirstName#%' AND

</cfif>



<cfif #form.lastname# is not "">

  Employee.LastName LIKE '#form.LastName#%' AND

</cfif>



<cfif #form.salary# is not "">

  Employee.Salary >= #form.Salary# AND

</cfif>



<cfif isdefined("Form.Contract")>

  Employee.Contract = 'Yes' AND

<cfelse>

  Employee.Contract = 'No' AND

</cfif>

  0=0



</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>

Save the page as getemp.cfm.
Open the file askemp.cfm in your browser and enter criteria into any fields, then submit the form.
Verify that the results meet the criteria you specify.

Reviewing the code

The action page getemp.cfm builds a SQL statement dynamically based on what the user enters in the form page AskEmp.cfm. The following table describes the highlighted code and its function:

CFML Code

Description

SELECT * FROM Employee WHERE
Get the records from the Employee table according to the following conditions.

<cfif #Form.FirstName# is not ""> Employee.FirstName LIKE '#form.FirstName#%' AND </cfif>
If the user entered anything in the FirstName text box in the form, add "AND Employee.FirstName LIKE '[what the user entered in the FirstName text box]%'" to the SQL statement. You can use the FirstName variable without ensuring its existence because text boxes pass an empty string if you do not enter text.

<cfif #Form.LastName# is not ""> Employee.LastName LIKE '#form.LastName#%' AND </cfif>
If the user entered anything in the LastName text box in the form, add "AND Employee.LastName LIKE '[what the user entered in the LastName text box]%'" to the SQL statement.

<cfif #Form.Salary# is not ""> Employee.Salary >= #form.Salary# AND </cfif>
If the user entered anything in the Salary text box in the form, add "AND Employee.Salary >= [what the user entered in the Salary text box]" to the SQL statement.

<cfif isDefined("Form.Contract")> Employee.Contract = 'Yes' AND <cfelse> Employee.Contract = 'No' AND </cfif>
If the user selected the Contractor check box, get data for the employees who are contractors; otherwise, get data for employees who are not contractors. The isdefined function test for the existence of the Form.Contract variable is needed because the variable only exists if they select the Contractor box.

0=0
If none of the conditions are true, the 0=0 statement ensures that the WHERE clause does not result in a SQL syntax error. Instead, the SELECT statement returns the entire table. Putting this statement at the end of the WHERE clause improves security by making it harder to attach extra SQL statements in a dynamic variable, and may affect the database's optimization of the SQL statement.

Developing ColdFusion Applications
Making Variables Dynamic

CFML Code	Description
SELECT * FROM Employee WHERE	Get the records from the Employee table according to the following conditions.
<cfif #Form.FirstName# is not ""> Employee.FirstName LIKE '#form.FirstName#%' AND </cfif>	If the user entered anything in the FirstName text box in the form, add "AND Employee.FirstName LIKE '[what the user entered in the FirstName text box]%'" to the SQL statement. You can use the FirstName variable without ensuring its existence because text boxes pass an empty string if you do not enter text.
<cfif #Form.LastName# is not ""> Employee.LastName LIKE '#form.LastName#%' AND </cfif>	If the user entered anything in the LastName text box in the form, add "AND Employee.LastName LIKE '[what the user entered in the LastName text box]%'" to the SQL statement.
<cfif #Form.Salary# is not ""> Employee.Salary >= #form.Salary# AND </cfif>	If the user entered anything in the Salary text box in the form, add "AND Employee.Salary >= [what the user entered in the Salary text box]" to the SQL statement.
<cfif isDefined("Form.Contract")> Employee.Contract = 'Yes' AND <cfelse> Employee.Contract = 'No' AND </cfif>	If the user selected the Contractor check box, get data for the employees who are contractors; otherwise, get data for employees who are not contractors. The `isdefined` function test for the existence of the Form.Contract variable is needed because the variable only exists if they select the Contractor box.
0=0	If none of the conditions are true, the 0=0 statement ensures that the WHERE clause does not result in a SQL syntax error. Instead, the SELECT statement returns the entire table. Putting this statement at the end of the WHERE clause improves security by making it harder to attach extra SQL statements in a dynamic variable, and may affect the database's optimization of the SQL statement.