If you just output the sqlStatement variable, how does it look? Also, any particular reason you are not just building your sql statement inside the <cfquery> tags?
On Tue, Jan 22, 2013 at 3:33 PM, Tom McNeer <tmcn...@gmail.com> 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:354007 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
