User Guide

Dynamic SQL 99
<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 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.