What version of CF? If it's 9+, you can use placeholders in the SQL string for the parameters, rather than <cfqueryparam> tags, passing the param data to the query separately. You can't use <cfquery> for this approach, but can use Query.cfc instead. It's one of the few areas in which Query.cfc is superior to <cfquery>.
That said, I'd like to echo the idea of creating these generic sort of queries isn't a great approach to things, so would strongly discourage this practice. -- Adam On 22 January 2013 20:33, Tom McNeer <[email protected]> wrote: > > Hi, > > I need to build up a complex dynamic query statement. I have built methods > to add queryParam statements, and built up valid SQL. > > If I do: > > <cfset sqlStatement = "SELECT DISTINCT tableName.ID FROM tableName > LEFT OUTER JOIN secondTable ON tableName.ID=secondTable.fkID > WHERE ( tableName.clientID = 'D35DAF11-DCB2-4341-B26C-0D31325CD51B' AND > tableName.otherID IN > (SELECT otherID FROM thirdTable WHERE (firstName LIKE > <cfqueryparam cfsqltype="cf_sql_varchar" value="%bar%" /> OR lastName LIKE > <cfqueryparam cfsqltype="cf_sql_varchar" value="%bar%" />)) > )" /> > > And then do: > > <cfquery name="foo"> > #sqlStatement# > </cfquery> > > > I get an error from the SQL Server driver that points to the first part of > the client ID - "Incorrect syntax near 'D35DAF11'" and a "nextException" > that says "Incorrect syntax near '<'." > > Yet if I paste the above statement directly into the cfquery tag, it runs > perfectly. > > Can anyone suggest where I'm going wrong, please? I suppose it could be a > single quotes problem of some sort. But certainly preserveSingleQuotes has > no affect. And if I wrap the first varchar (the clientID) in a queryParam > statement, it simply eliminates the first error and immediately shows the > "Incorrect syntax near '<'." error. > > -- > Thanks, > > Tom > > Tom McNeer > MediumCool > http://www.mediumcool.com > 1735 Johnson Road NE > Atlanta, GA 30306 > 404.589.0560 > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:354019 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
