Dynamically Generating SQL Statements

As you've already learned, you can retrieve a record for every employee in a database table by composing a query like this:

<CFQUERY NAME="GetEmployees" DATASOURCE="CompanyInfo">  
    SELECT  FirstName, LastName, Contract
    FROM   Employees
</CFQUERY>

But when you want to return information about employees that match user search criteria, you use the SQL WHERE clause with a SQL SELECT statement to compare a value against a character string field. When the WHERE clause is processed, it filters the query data based on the results of the comparison.

For example, to return employee data for only employees with the last name of Allaire, you would build a query that looks like this:

<CFQUERY NAME="GetEmployees" DATASOURCE="CompanyInfo">  
    SELECT FirstName, LastName, Contract
    FROM    Employees
    WHERE  LastName = 'Allaire'
</CFQUERY>

However, instead of putting the LastName directly in the SQL WHERE clause, you can use the text the user entered in the form for comparison:

<CFQUERY NAME="GetEmployees" DATASOURCE="CompanyInfo">
    SELECT FirstName, LastName, Salary
    FROM Employees
    WHERE LastName='#Form.LastName#'
</CFQUERY>