Conditional sort order in SQL Server windowed function clauses

Question

So, this is not your average 'conditional sort by' question... I have a rather tricky problem here. :-) I want to allow my stored procedure to offer a conditional sort order for the results. Normally this can be done in the following manner:

SELECT *
INTO #ResultsBeforeSubset
FROM
    MyTable
ORDER BY
    CASE WHEN @SortAscending=1 THEN 'SortColumn' END ASC,
    CASE WHEN @SortAscending=0 THEN 'SortColumn' END DESC

I'd like to do a CASE statement around the actual ASC/DESC, but that doesn't work. The reason the above method works is because, when @SortAscending isn't equal to the given value, SQL server translates the CASE statement into the constant NULL. So, if @SortAscending is 0, you effectively have:

ORDER BY
    NULL ASC,
    SortColumn DESC

The first sort expression, then, just does nothing. This works because in a regular SELECT statement you can use constant in an ORDER BY clause.

Trouble is, the time that I'm sorting in my stored proc is during a SELECT statement which contains a windowed function ROW_NUMBER(). I therefore want to put the CASE statement inside its OVER clause, like so:

SELECT *
INTO #ResultsBeforeSubset
FROM (
    SELECT
        ROW_NUMBER() OVER (
            ORDER BY
                CASE WHEN @SortAscending=1 THEN rowValues.[SortColumn] END ASC,
                CASE WHEN @SortAscending=0 THEN rowValues.[SortColumn] END DESC
        ) AS RowNumber,
        *
    FROM (
        -- UNIONed SELECTs returning rows go here...
    ) rowValues
) rowValuesWithRowNum

Unfortunately, this causes the following error when you run the stored procedure:

Windowed functions do not support constants as ORDER BY clause expressions.

Because this is the clause of a windowed function, the conversion of the CASE statement to the constant NULL is invalid.

Can anyone think of a way that I can conditionally vary the sort order of UNIONed SELECTs, and assign row numbers to each row resulting from these sorted results? I know I could resort to constructing the entire query as a string and execute that as fully dynamic SQL, but I'd rather avoid that if possible.

---

UPDATE: Looks like the problem wasn't caused by the CASE statement per se, but by the fact that I was using only constant values in the CASE statement's conditional clause. I've started up a new question on this curious behaviour here.

Answer 1

You could use constants if you wrap them in a SELECT, such as:

OVER( ORDER BY (SELECT NULL) )

So in your case you should be able to do:

SELECT
    ROW_NUMBER() OVER (
        ORDER BY
            (SELECT CASE WHEN @SortAscending=1 THEN rowValues.[SortColumn] END) ASC,
            (SELECT CASE WHEN @SortAscending=0 THEN rowValues.[SortColumn] END) DESC
    ) AS RowNumber,