Concatenating SQL Clauses
Now try entering text in two search fields, or all three of them. What happens? You probably generated an error like the one in Figure 12.15.
Figure 12.15. Dynamic SQL must be generated carefully to avoid building invalid SQL.
Why did this happen? Well, suppose the was specified as the MovieTitle and 2 as the RatingID. Walk through the <cfif> statements to work out what the generated SQL would look like. The first condition will be TRUE, the second will be FALSE, and the third will be TRUE. The SELECT statement would therefore look like this:
SELECT MovieTitle, PitchText, Summary, DateInTheaters ...