Oh my goodness me, still wrong! Should be: SELECT email FROM user u WHERE country = <cfqueryparam cfsqltype="cf_sql_varchar" value=#somecountry#> AND ( Len('<cfqueryparam cfsqltype="cf_sql_varchar" value=#somestate#>') = 0
OR state = <cfqueryparam cfsqltype="cf_sql_varchar" value=#somestate#>) Phew! Dom On 24/10/2007, Dominic Watson <[EMAIL PROTECTED]> wrote: > > Woops, I made a mistake in that SQL, should have been: > > <cfquery name="myname" datasource="myDB"> > > SELECT email > FROM user u > WHERE country = <cfqueryparam cfsqltype="cf_sql_varchar" > value=#somecountry#> > AND ( Len(<cfqueryparam cfsqltype="cf_sql_varchar" value=#somestate#>) = > 0 > OR state = <cfqueryparam cfsqltype="cf_sql_varchar" value=#somestate#>) > </cfif> > </cfquery> > > On 24/10/2007, Dominic Watson <[EMAIL PROTECTED]> wrote: > > > > If you are talking about whether, in the case of your multiple IF > > statement DSQL, to use or not use CFQP then I think there is only one > > answer; use CFQP! As I said before, if there is a performance boost in using > > CFQP over not using it, then that performance boost should still be valid. > > Also, all the arguments of security probably outweigh any performance issues > > there may be. > > > > If you are talking about finding an alternative to the multiple IF DSQL > > then I would say that if it is at all possible, then do it! I.e., in the > > example you gave, you could replace the CFIF with this: > > > > <cfquery name="myname" datasource="myDB"> > > > > SELECT email > > FROM user u > > WHERE country = <cfqueryparam cfsqltype="cf_sql_varchar" > > value=#somecountry#> > > AND ( Len(<cfqueryparam cfsqltype="cf_sql_varchar" value=#somestate#>) > > > 0 > > OR state = <cfqueryparam cfsqltype="cf_sql_varchar" > > value=#somestate#>) > > </cfif> > > </cfquery> > > > > Dom > > > > On 24/10/2007, Ben Mueller <[EMAIL PROTECTED] > wrote: > > > > > > Thanks, all, for your replies. So, a follow-up question: will SQL > > > create multiple prepared statements for each condition? If so, that's > > > great. My fear is that it will only create 1 prepared statement for each > > > cfquery block, so if the current statement doesn't match the previous > > > statement, it would discard the previous one and create a new one. > > > > > > I have a bunch of queries that have 8 or more <cfif> conditions, > > > almost all in the WHERE clause. Obviously, the more <cfif> conditions > > > there > > > are, the greater the likelihood that any one individual call won't match > > > the > > > previous one. > > > > > > I know cfqp is really good and all that, but this would be pretty > > > annoying if I got no real performance benefit. > > > > > > > > > > > > > > > > >Yes, to the extent that you are less likely to have another query > > > with the > > > >same prepared statement (and the same execution plan, of course) that > > > can be > > > >run again. Frankly, though, I wouldn't worry too much about that, > > > since > > > >coding around that causes all kinds of problems. > > > > > > > >Dave Watts, CTO, Fig Leaf Software > > > >http://www.figleaf.com/ > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291985 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4