I know how to add an optional parameters to an sql query

it is done like this

select * from test t where (?1 is null or t.myColumn = ?1)

where ?1 is a parameter which you can pass from language such as java (i.e using named queries), the advantage is that I don't need to use ORM nor I need manually build (concatenate) strings into query.

is there anything similar to make ASC and DESC optional?

CodePudding user response：

No, you will have to use dynamic SQL (statements constructed on the fly) for that.

There is a trick you could use for numeric columns:

... ORDER BY col * CASE WHEN $1 = 'DESC' THEN -1 ELSE 1 END