Occasionally I have the problem that I need to run as query with a form of dynamic sorting - meaning sometimes ascending or descending and sometimes based on an ordernumber or a date, for example.

Now a CASE statement in the order by is often very effective for handeling such issues, but I've not found it possible to combine it with the Ascending and Descending clasuses (yet, could be that I'm just doing it wrong). 

For example suppose we have a table which contains a "Date" field of the type DateTime, and we need to be able to sort this both descending and ascending based on some input parameter. Instead of making two queries, one might think that something like this would work

DECLARE @ASC BIT
SET @ASC = 1
SELECT *
FROM DateTimeOrderTest
ORDER BY DATE
    CASE @ASC
        WHEN 1 THEN ASC
        ELSE DESC
    END

But well - it doesn't: "Incorrect syntax near the keyword 'CASE'."

Or one might think:

DECLARE @ASC BIT
SET @ASC = 1
SELECT *
FROM DateTimeOrderTest
ORDER BY
    CASE @ASC
        WHEN 1 THEN Date ASC
        ELSE Date DESC
    END

Which gives a nice little: Incorrect syntax near the keyword 'ASC'

A little fix to get around this would be to not utilize case, but instead make the Ascending and Descending a factor so when Ascending you have a factor of 1 and Descending you have a factor of -1. Then simply convert/cast the Date to an integer (which should not be a terrible expensive procedure, as the datetime is represented as a number anyway, I wonder if it is possible to somehow use the internal representation for my purpose, or if the database does that when casting as integer....oh, well), and multiply the factor to the order by.

This mean the following two queries give the same result:

SELECT  *
FROM DateTimeOrderTest
ORDER BY CAST(Date AS INT) * -1

SELECT *
FROM DateTimeOrderTest
ORDER BY Date DESC

This technique of multiplying either 1 or -1, can be used in many queries, and of course is not limited to datetimes.

Anyways, I've found it quite useful myself.