alpha列的条件SQL ORDER BY ASC / DESC

Writing a stored procedure in MS SQL Server 2008 R2, I want to avoid using DSQL...

在MS SQL Server 2008 R2中编写存储过程，我想避免使用DSQL ...

I would like the sort method (ASC or DESC) to be conditional.

我希望sort方法（ASC或DESC）是有条件的。

Now, with a numeric column I would simply use a case statement and negate the value to emulate ASC or DESC... That is:

现在，使用数字列，我只需使用case语句并取消值来模拟ASC或DESC ......即：

... ORDER BY CASE @OrderAscOrDesc WHEN 0 THEN [NumericColumn] ELSE -[NumericColumn] END ASC

What is an appropriate method for doing this with an alpha column?

使用alpha列执行此操作的适当方法是什么？

EDIT: I thought of a clever way but it seems terribly inefficient... I could insert my ordered alpha column into a temp table with an autonumber then sort by the autonumber using the method described above.

编辑：我想到了一个聪明的方法，但它看起来非常低效...我可以将我的有序alpha列插入带有自动编号的临时表中，然后使用上述方法按自动编号排序。

EDIT2:

EDIT2：

What do you guys think of this approach?

你们怎么看待这种方法？

ORDER BY CASE @OrderAscOrDesc WHEN 0 THEN [AlphaColumn] ELSE '' END ASC,
CASE @OrderAscOrDesc WHEN 0 THEN '' ELSE [AlphaColumn] END DESC

I don't know if forcing a sort on a uniform column is more efficient than deriving numbers from sorted strings though

我不知道是否强制对统一列进行排序比从排序字符串中导出数字更有效

2 个解决方案

#1

One option

一种选择

;WITH cQuery AS
(
   SELECT
       *,
       ROW_NUMBER() OVER (ORDER BY SortColumn) AS RowNum
   FROM
       MyTable
)
SELECT
   *
FROM
   cQuery
ORDER BY
   RowNum * @Direction --1 = ASC or -1 = DESC

Or CASE which IMHO is a bit uglier

或者IMHO哪个有点丑陋

ORDER BY
  CASE WHEN 'ASC' THEN SortColumn ELSE '' END ASC,
  CASE WHEN 'DESC' THEN SortColumn ELSE '' END DESC

#2

This is one of those cases when specific solutions may be preferable preferable to generic ones, especially when we deal with large amounts of data. I would:

这是特定解决方案可能优于通用解决方案的情况之一，尤其是当我们处理大量数据时。我会：

IF @OrderAscOrDesc = 0 THEN BEGIN
  SELECT ...
  FROM  ... 
  ORDER BY [AlphaColumn] ASC
END ELSE BEGIN
  SELECT ...
  FROM  ... 
  ORDER BY [AlphaColumn] DESC
END

If you have an index on [AlphaColumn], you might sometimes get a better plan with a more specific query, than with a generic one-size-fits-all one.

如果你在[AlphaColumn]上有一个索引，你有时可能会得到一个更具体的查询，而不是通用的一个通用的查询。

Edit: to facilitate code reuse, you can wrap your select in an inline UDF - it will perform just as well:

编辑：为了方便代码重用，您可以将select包装在内联UDF中 - 它也会执行：

IF @OrderAscOrDesc = 0 THEN BEGIN
  SELECT ...
  FROM  YourInlineUdf(...)
  ORDER BY [AlphaColumn] ASC
END ELSE BEGIN
  SELECT ...
  FROM  YourInlineUdf(...) 
  ORDER BY [AlphaColumn] DESC
END

#1