var q = new com.adobe.coldfusion.query(); q.setDatasource("cfartgallery"); var sql = "select mediatype from media"; if(structKeyExists(arguments, "something")) { sql &= " where media like :search"; q.addParam(name="search",value="%#arguments.str#%",cfsqltype="cf_sql_varchar"); } q.setSQL(sql); var results = q.execute().getResult();
Does that make sense? On Tue, Apr 26, 2011 at 1:32 PM, Kyle McLean <kmclea...@gmail.com> wrote: > > I've been working on a CF9 project, and I'm trying to take full advantage of > the cfscript syntax for my components. I have many cases where I'm using > dynamic SQL based on the existence of an optional argument in a function. In > CF8 and below it'd look something like this: > > <cffunction name="testFunc" returntype="query"> > <cfargument name="testArg" type="string" required="false" /> > > <cfquery name="testQuery" datasource="testDSN"> > SELECT > * > FROM > TBL > <cfif structKeyExists(arguments.testArg)> > WHERE > COL = '#arguments.testArg#' > </cfif> > </cfquery> > > <cfreturn testQuery /> > </cffunction> > > Is there an elegant way to do the same thing using cfscript? I haven't had > any luck with string concatenation in the setSQL method of the query object > (and it's kind of clunky to boot). I'd also hate to have to write two > almost-identical blocks of code one with the where clause and one without and > then apply the correct string to setSQL at runtime, although I assume that > would solve my problem. > > I'd also like to use query binding, just to make things more complicated. Is > there some sort of cleaner approach that I'm missing, or is this just one of > the downsides to using cfscript? > > Thanks in advance! > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344004 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm