This seems appropriate here, how do I implement an if statement in a sql query using queryExecute? Is it possible? Or do I bite the bullet and craft the sql in a var and use that var in the queryExecute call. I ask because I am modernizing and there are a LOT of queries like this - figured I'd "do it right" from the beginning.

<cfquery name="sortedQry" dbtype="query">
		select *
		from tmpQry
		order by 
		<cfif arguments.sortBy eq 0>
			volume
		<cfelse>
			length, width, height
		</cfif>
</cfquery>

i usually append stuff together and just drop the variable into queryExecute(), because when i'm appending based on conditionals, it usually also involves appending params, but

#( arguments.sortBy eq 0 ? 'volume' : 'length, width, height' )#

should do what your example is doing.

Thanks, for this simple one I can get away with that, but it looks like I am in the sql statement and param struct business now. At least it will be easier to read than they currently are.

If you can use QB by Ortus, I highly recommend it. It solves syntax irritations like these.

I use something like this in all the CF statements I write. You can use CFIF or CFCASE, or nest the two, or anything else. Unless its a relatively small table you will want to use SElect, column names from the table You can use the args to do anything from sort order, joins, so on and so forth. There is no end to how extendable this is, other than the time you want to spend writing code and what you want your query to do. The reason I would keep the tag format is, what if you need to have an SQL developer help you debug your statement. It's easier to read in simple tag format as it is far closer to the actual SQL statement and its far easier to easily take SQL developer's SQL statements and place them in ColdFusion. Give you an idea of how complex you can make your statement with a single pass. <cfquery name="myQuery" datasource="myDatasource"> SELECT column1, column2, column3, column4, column5 FROM sample WHERE columnName = <cfqueryparam value="#inputSample#" cfsqltype="cf_sql_varchar"> <cfif inputSample EQ "apple"> UPDATE sample SET inputsample1 = UCase(inputsample1) <cfelseif inputSample EQ "banana"> <cfif dayOfWeek EQ "Monday"> SELECT * FROM sample_2 WHERE customerName LIKE '%Orange%' <cfelseif dayOfWeek EQ "Wednesday"> SELECT * FROM sample_2 WHERE customerID LIKE '%7' </cfif> </cfif> </cfquery> This still is readable to the average SQL Developer, and follows the ancient coldfusion holy texts (HHAHAH) where as the script version, is q = new Query(); q.setDataSource("myDatasource"); q.addParam(name="inputSample", value=inputSample, cfsqltype="cf_sql_varchar"); q.addSql("SELECT c1, c2, c3, c4, c5 FROM s WHERE n = :inputSample"); if (inputSample EQ "apple") {q.addSql("UPDATE s SET i1 = UCase(i1)");} else if (inputSample EQ "banana") { if (dayOfWeek EQ "Monday") { q.addSql("SELECT * FROM s2 WHERE cn LIKE '%Orange%'"); } else if (dayOfWeek EQ "Wednesday") { q.addSql("SELECT * FROM s2 WHERE ci LIKE '%7'"); } } result = q.execute().getResult();

QueryExecute i would argue makes the cfscript version much nicer.. then both the cfquery version and the old new Query() method... https://cfdocs.org/queryexecute

I am not here to start the very old holywar of script vs tag. If it works for you, it works 🙂

For my simple example, I went with this

var tmpQry=arrayOfStructuresToQuery(arguments.boxStack);

saveContent variable="thesql" {

writeOutput('select * from tmpQry order by  #( arguments.sortBy eq 0 ? 'volume' : 'length, width, height' )#');

}

sortedQry=queryExecute(theSql,{},{dbType='query'});

return QueryToArrayOfStructures(sortedQry);