Conditional SQL ORDER BY ASC/DESC for alpha columns

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

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

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

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

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

EDIT: I thought of a clever way but it seem开发者_如何学Pythons 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.

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

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

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

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.

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

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