SQL Server dynamic ORDER BY with mixed datatypes: is this a good idea?

Question

I have stored procedure that returns results sorted dynamically. The parent folder (this is for content management) has a RankTypeID field that allows sorting by Rank (0), Start Date in ascending order (1), Start Date in descending order (2), and document title (3)

Rank is an integer, date is smalldatetime, and title is a nvarchar.

...

ORDER BY
    Case Parent.RankTypeID
      When 0 Then dbo.Folders.Rank
      When 1 Then Cast(dbo.Documents.SortableDateStart As bigint)
      When 2 Then (1 - Cast(dbo.Documents.SortableDateStart As bigint))
      When 3 Then Cast(dbo.Documents.Title as sql_variant)
    End

I set up the SortableDateStart as a computed column to take a DateStart smalldatetime column and convert it into a bigit for sorting. It takes an ISO8601 date (designed for xml usage, and also handy for sorting) and replaces the T, :, and -

(replace(replace(replace(CONVERT([varchar](16),[DateStart],(126)),'T',''),'-',''),':',''))

This is kind of ugly. Is there a better way to do this? I'm also open to better ways of handling this dynamic sorting.

Edit: Test Data Setup

DECLARE @Temp TABLE
(
[Rank] int,
[Title] nvarchar(100),
[DateStart] datetime
)

INSERT into @Temp
SELECT 1, 'title1', '1/1/2010 10:01:00AM'
UNION
SELECT 2, 'atitle1', '1/1/2010 10:03:00AM'
UNION
SELECT 3, 'title1', '1/1/2010 10:10:00AM'
UNION
SELECT 4, 'btitle1', '1/1/2010 10:04:00AM'
UNION
SELECT 10, 'title1', '1/1/2010 10:07:00AM'
UNION
SELECT 11, 'dtitle1', '1/1/2010 10:09:00AM'
UNION
SELECT 12, 'ctitle1', '1/1/2010 10:00:01AM'
UNION
SELECT 13, 'title1', '1/1/2010 10:10:00AM'

DECLARE @RankTypeID tinyint
--SET @RankTypeID = 0 -- rank
--SET @RankTypeID = 1 -- date start asc
SET @RankTypeID = 2 -- date start desc
--SET @RankTypeID = 3 -- title

SELECT 
    [Rank],
    [DateStart],
    [Title]
FROM
    @Temp
ORDER BY
    Case @RankTypeID
      When 0 Then [Rank]
      When 1 Then Cast([DateStart] As sql_variant)
      When 3 Then [Title]
      else null
    End,      
    Case @RankTypeID
      When 2 Then Cast([DateStart] As sql_variant)
    End DESC

Answer 1

Try something like this

ORDER BY
    Case Parent.RankTypeID
      When 0 Then dbo.Folders.Rank
      When 1 Then dbo.Documents.DateStart
      When 3 Then Cast(dbo.Documents.Title as sql_variant)
      else null
    End,      
    case Parent.RankTypeID
      when 2 Then dbo.Documents.DateStart
    end desc

Update.

No, you don't need to cast anything. Here's a full solution for your test data.

order by 
case @RankTypeID when 0 then [Rank] else null end,      
case @RankTypeID when 1 then [DateStart] else null end,
case @RankTypeID when 2 then [DateStart] else null end desc,
case @RankTypeID when 3 then [Title] else null end 

Answer 2

One way is to decouple the sort value and the actual ORDER BY

SELECT
   col1, col2, ...
FROM
   (
   SELECT
      col1, col2, ...,
      ROW_NUMBER() OVER (ORDER BY Rank) AS RankASC,
      ROW_NUMBER() OVER (ORDER BY DateStart) AS DateStartASC,
      ROW_NUMBER() OVER (ORDER BY Title) AS TitleASC
   FROM
      MyTable
   ) foo
ORDER BY
    Case foo.RankTypeID
      When 0 Then foo.RankAsc
      When 1 Then foo.DateStartAsc
      When 2 Then -1 * foo.DateStartAsc
      When 3 Then foo.TitleAsc
      --else null needed?
    End

If you want to define @SortOrder (or as a column) as 1 = ASC, -1 = DESC then you can do this

SELECT
   col1, col2, ...
FROM
   (
   SELECT
      col1, col2, ...,
      ROW_NUMBER() OVER (ORDER BY Rank) AS RankOrder,
      ROW_NUMBER() OVER (ORDER BY DateStart) AS DateStartOrder,
      ROW_NUMBER() OVER (ORDER BY Title) AS TitleOrder
   FROM
      MyTable
   ) foo
ORDER BY
    @SortOrder *
      Case foo.RankTypeID
        When 0 Then foo.RankOrder
        When 1 Then foo.DateStartOrder
        When 3 Then foo.TitleOrder
      End