Building a query param dynamically

Question

Hi I am trying to build a query parameter dynamically, and am getting an error, i am using the code below,

<cfset featQuery="">
<cfloop list="#arguments.uid_features#" index="x">
<cfif x neq "0">
<cfif Len(featQuery) gt 0>
<cfset featQuery = featQuery& " AND ">
</cfif>
<cfset featQuery = featQuery & 'uid_prodf_featid = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="' & x & '">'>
</cfif>
</cfloop>

I get this error message from coldfusion; [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '<'.

If i look at the output, it looks correct, but normaly using cfquerypram, you just get (param1), uid_prodf_featid=(param1) in the error message it displays the following;

uid_prodf_featid = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="5">

Jason

Jake Feasel Jake Feasel · Accepted Answer · 2011-12-19T23:42:33

You can't really build and execute CFML dynamically like you're attempting to do. It looks to me like you're trying to build a SQL query outside of a cfquery tag context; this would be fine, except for your need to parameterize it. If possible, change your code to run within a cfquery tag pair:

<cfquery...>
SELECT * FROM tableFoo
<cfif ListLen(arguments.uid_features)>
 WHERE uid_prodf_featid IN (<cfqueryparam value="#arguments.uid_features#" list="true" cfsqltype="CF_SQL_INTEGER">)
</cfif>
</cfquery>

Also, as you can see I've changed your query structure a bit - you had a lot of code to do something that is much more easily accomplished as I show above.

edit

I see that you actually are doing AND operations with each item in your uid_features list... I have a hard time imagining there being a valid logical reason for that (rather than OR), but if so, my example won't work for that - instead change it back to a series of AND conditions within the loop.

Building a query param dynamically

1 Answers