Coldfusion: executing dynamic query containing cfqueryparam

Question

First, the code. I'm making a struct of queries:

<cfset myQueryStruct = { 
qone = "select * from t1 where column = <cfqueryparam cfsqltype='cf_sql_varchar' value='#arguments.a1#'>",
qtwo = "select * from t2 where column = <cfqueryparam cfsqltype='cf_sql_varchar' value='#arguments.a1#'>"
}>

I need to execute the queries dynamically like below:

<cfquery name="qName" datasource="#dsn#">
    #myQueryStruct[arguments.type]#
</cfquery>

But I am getting an error:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '<'. 

It seems the error is related with "<cfqueryparam cfsqltype='cf_sql_varchar' value='#arguments.a1#'>" in the query("<")

I tried using evaluate() and is getting this error:

Invalid CFML construct found on line 1 at column 15.
ColdFusion was looking at the following text:

t1

I know we can do this by using <cfif> ladder inside <cfquery> but my question is, can we do this like the way I mentioned(I must be missing something) or is it not possible in CFML?

Answer 1

The problem is, that you are passing a string with a tag that must be processed by ColdFusion. But you are too late. CF has already parsed the template, so the string remains untranslated and therefore throws an SQL error.

I'm not sure, what exactly you would like to accomplish here, maybe something like this code would work:

<cfscript>
dsn = "myDatabase";
criteria = "something";
myQueryStruct = {
    qone = "select * from t1 where column = ( :a1 ) ",
    qtwo = "select * from t2 where column = ( :a1 ) "
};

result1 = getQueryResult( queryString = myQueryStruct[ "qone" ], filterValue = criteria, dsn = dsn );
result2 = getQueryResult( queryString = myQueryStruct[ "qtwo" ], filterValue = criteria, dsn = dsn );

public function getQueryResult( queryString, filterValue, dsn ){
    var qry = new Query();
    var qryString = arguments.queryString;
    var queryResult = "";
    qry.setDatasource( arguments.dsn );
    qry.setSQl( qryString );
    qry.addParam( name="a1", value= arguments.filterValue, cfsqltype="cf_sql_varchar" ); 
    queryResult = qry.execute().getResult();
    return queryResult;
}
</cfscript>

Answer 2

You could use cfparam to establish the arguments.a1 and type of data first and then just reference the variables as '#arguments.a1#' within the query. It should help you at least figure out if it is the cfqueryparam tag that is causing the problem.