Removing duplication in dynamic ROW_NUMBER() OVER

问题:

I have the following snippet from a SQL statements

ROW_NUMBER() OVER (ORDER BY CASE
    WHEN @SortBy = 'column1 ASC' THEN cast(column1 AS sql_variant)
    WHEN @SortBy = 'column2 ASC' THEN cast(column2 AS sql_variant)
    WHEN @SortBy = 'column3 ASC' THEN cast(column3 AS sql_variant)
    WHEN @SortBy = 'column4 ASC' THEN cast(column4 AS sql_variant)
    ELSE NULL
END ASC,
CASE
    WHEN @SortBy = 'column1 DESC' THEN cast(column1 AS sql_variant)
    WHEN @SortBy = 'column2 DESC' THEN cast(column2 AS sql_variant)
    WHEN @SortBy = 'column3 DESC' THEN cast(column3 AS sql_variant)
    WHEN @SortBy = 'column4 DESC' THEN cast(column4 AS sql_variant)
    ELSE NULL
END DESC) AS RowNumber

It works, but it's quite repetitive, is there a way to make the ASC/DESC dynamic as well, so I don't need the duplicated CASE statement?

回答1:

One way you could do this would be to define the column alias at a different level so you can reference it twice without repeating the expression.

SELECT *,
       Row_number() OVER (ORDER BY 
                 CASE WHEN @SortBy LIKE '% ASC' THEN sort_col END ASC, 
                 CASE WHEN @SortBy LIKE '% DESC' THEN sort_col END DESC) AS RowNumber
FROM   YourTable
       CROSS APPLY (SELECT CASE
                             WHEN @SortBy LIKE 'column1 %' THEN Cast(column1 AS SQL_VARIANT)
                             WHEN @SortBy LIKE 'column2 %' THEN Cast(column2 AS SQL_VARIANT)
                             WHEN @SortBy LIKE 'column3 %' THEN Cast(column3 AS SQL_VARIANT)
                             WHEN @SortBy LIKE 'column4 %' THEN Cast(column4 AS SQL_VARIANT)
                           END) C(sort_col) 

I would consider using dynamic SQL for this instead though. This kind of catch all query will kill the idea of getting a good plan that can use indexes to avoid a sort.

回答2:

I'm not sure if you will think this is simpler. It uses arithmetic manipulation to get the result, based on the fact that the descending row number is the total count minus the ascending:

((case when @SortBy like '%DESC' then 1 else -1 end) *
 (case when @SortBy like '%DESC'
       then count(*) over ()
       else 0
  end) -
 (ROW_NUMBER() OVER
      (ORDER BY CASE WHEN @SortBy like 'column1%' THEN cast(column1 AS sql_variant)
                     WHEN @SortBy like 'column2%' THEN cast(column2 AS sql_variant)
                     WHEN @SortBy like 'column3%' THEN cast(column3 AS sql_variant)
                     WHEN @SortBy like 'column4%' THEN cast(column4 AS sql_variant)
                     ELSE NULL
                 END)
 )
)

It does limit the list of columns to one case statement.