How to use placeholder in order by in a query?

[Vishnu22410012h6s8]

dailyStatus = "select * from MyTasks ";

if(Len(custom.ORDER)){

            dailyStatus = dailyStatus & custom.ORDER;

        }

        else if (isDefined("URL.OrderBy") AND Len(URL.OrderBy)){

            dailyStatus = dailyStatus & " ORDER BY " & "?" & " " & "?";

            arrayAppend(params,{value:URL.OrderBy,cfsqltype:"cf_sql_varchar"});

            arrayAppend(params,{value:URL.Direction,cfsqltype:"cf_sql_varchar"});

        }

        else{

            dailyStatus = dailyStatus & "ORDER BY TimeRemaining, LastModifiedDate";

        }

        qryList = QueryExecute(dailyStatus, params, {datasource = REQUEST.CFG.DS});

This doesn't seem to work , because I'm getting error,

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

[Dave Watts]

Can you show the generated value for dailyStatus?

[Charlie Arehart]

And while you're at it, you can writedump also the params array, to see what's in that 3rd element. 

Do both of these just before the final queryexecute line, of course--and comment out that line to prevent the error, if it blocks you seeing the output. You may even need to add an abort; after the dump, to prevent it going elsewhere after this.

And if somehow you're doing this on prod (you have not setup a local dev cf), note that you can change the writeOutput of the dailystatus string to use writelog, and then modify the writeDump to name an output file. Both would prevent showing the info on screen. See the cf docs for details. 

[BKBK]

The error is probably caused by the line

 dailyStatus = dailyStatus & custom.ORDER;

because custom.ORDER is not a valid ORDER BY clause. 

Also, shouldn't you have an initialization such as the following somewhere?

params=[];

Yet another consideration: there are 2 parameters, so replace

  else if (isDefined("URL.OrderBy") AND Len(URL.OrderBy)){

with something like

  else if (isDefined("URL.OrderBy") AND len(URL.OrderBy) GT 0 and isDefined("URL.Direction") AND len(URL.Direction) GT 0){

[BKBK]

Hi @Vishnu22410012h6s8 , was the problem resolved?