asked on

cfqueryparam dynamic sql

Suppose I have a
<cfoutput>
<cfsavecontent variable="sqlWhere"
and x = <cfqueryparam value=#x# ...>
and y = <cfqueryparam value=#y# ...>
and z = <cfqueryparam value=#z# ...>
</cfsavecontent>
</cfoutput>

can I include this in a query

<cfquery name='testme" datasource="MM2005">
select * from myschema.mytable
where
1=1
and .... = ....
#preservesinglequotes(sqlWhere)#
</cfquery>

Is this possible...
(seems an app I am working on has several nested loops which are running basically 4 similar queries for a total 13 times each).

In the itermim
I would like to seperate the where clause into 2 where clauses and add the same where clause portion to each query appending to whatever is different in the where clauses.

sorry if it doesn't make sense, I just need to see how I can add <cfqueryparam's to a query when they are part of a variable (sqlWHERE).

mrichmon

It should work, but

Why do you need to do it that way?

Why not have the dynamic portion of the where in the query like so:

<cfquery name='testme" datasource="MM2005">
select * from myschema.mytable
where
1=1
and
<cfif condition is true>
x = <cfqueryparam value=#x# ...>
and y = <cfqueryparam value=#y# ...>
and z = <cfqueryparam value=#z# ...>
<cfelse>
a = <cfqueryparam value=#a# ...>
and b = <cfqueryparam value=#b# ...>
and c = <cfqueryparam value=#c# ...>
and d = <cfqueryparam value=#d# ...>
</cfif>
</cfquery>

astro26

ASKER

There are about 5 if statements different conditions which determine what displays in query. I made it one to keep it simple.

Rather than have all of these if statements being evaluated 52 times, I wanted to evaluate them one time, store the results in a sqlWhere variable and append that veriable to the cfquery tag that gets run 52 times.

if not compare(x,0)
do this cfparam
/cfif

if not compare(y,'red')
do this cfparam
/cfif

if not compare(z,'babelfish')
do this cfparam
/cfif

if not compare(zz,'abracadabra')
do this cfparam
/cfif

astro26

ASKER

Suppose I have a
<cfoutput>
<cfsavecontent variable="sqlWhere"
and x = <cfqueryparam value=#x# ...>
and y = <cfqueryparam value=#y# ...>
and z = <cfqueryparam value=#z# ...>
</cfsavecontent>
</cfoutput>

can I include this in a query

<cfquery name='testme" datasource="MM2005">
select * from myschema.mytable
where
1=1
and .... = ....
#preservesinglequotes(sqlWhere)#
</cfquery>

this does not work. I get a <cfqueryparam must be placed inside a cfquery tag error.

Therefore, I converted all the < to #chr(60)# and the > to #chr(62)#, but I get another error
Macromedia][Oracle JDBC Driver][Oracle]ORA-00936: missing expression
I am assuming that the cfquery tag has started to be evaluated and instead of seeing (param1) it sees <cfparam , but this should have already been parsed by the coldfusion tag (parser) preprocessor or whatever it is called.

astro26

ASKER

<cfoutput>
<cfsavecontent variable="sqlWhere"
and x = #chr(60)#cfqueryparam value=#x# ...#chr(62)#
and y = #chr(60)#cfqueryparam value=#y# ...#chr(62)#
and z = #chr(60)#cfqueryparam value=#z# ...#chr(62)#
</cfsavecontent>
</cfoutput>

but this returns the error listed in the post above.
<cfquery name='testme" datasource="MM2005">
select * from myschema.mytable
where
1=1
and .... = ....
#preservesinglequotes(sqlWhere)#
</cfquery>

ASKER CERTIFIED SOLUTION

mrichmon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

astro26

ASKER

Just so I understand correctly, there is no way to dynamically add cfqueryparams to a cfquery tag unless they are already in a cfquery tag, thus must be manually entered and have switch/if statements around them?

mrichmon

As far as I understand....

mmc98dl1

I second that to mrichmon, you would need to put cfswitch/cfcase inside the query, then put in your cfqueryparams within the cfcase.