Creating a dynamic ORDERBY in T-SQL

I'm in the midst of converting a Crystal Report into an Excel file output... While there is an increased benefit of more control over the output, however, I lose many features that Crystal has to offer. After weighting options, let's just say its much easier to create an Excel file for this particular report.

Anyway, in Crystal Reports, I had the luxury of dynamically sorting by various datatypes. Based on the sort type the user selected, it could be a combination of DateTimeStamp, ID, and UserName... in this case, we're talking about three different datatypes: datetime, int, and varchar respectfully. We may create a ORDER BY clause with DateTimeStamp ASC, ID DESC, UserName ASC or in whatever combination you want.

This is the minimum requirement of the sort options from the client application (all in ascending):

• DateTimeStamp, UserName
• UserName, ID, DateTimeStamp
• ID, DateTimeStamp

"No problem," I said, "Piece of cake." I can pass the @OrderBy parameter to a SQL stored procedure from the client and then we'll have the sorted data back to the client app as an Excel spreadsheet.

DECLARE @OrderBy int
SET @OrderBy = 1
SELECT 
    DateTimeStamp, ID, UserName 
FROM 
    [Table1]
ORDER BY
    CASE @OrderBy 
        WHEN 0 THEN DateTimeStamp ASC, UserName ASC 
        WHEN 1 THEN UserName ASC, ID ASC, UserName ASC 
        WHEN 2 THEN DateTimeStamp ASC, ID ASC 
    END

But the sort isn't perfect, something's always off, you know how "2" is always after "10" in Windows/SQL Server... something like that. Converts characters the same datatype as the first column, even tried convert(varchar(20), datetimestamp, 110), etc. It became clear that we cannot dynamically order columns with various datatypes like this! Yeap, it's always the little things. *sigh*

Looked it up online thinking there's a quick solution, and saw some people had suggestions here and there. So tried various queries like the ones in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=5942 and none of them worked for me. T-SQL whines about having bad datatypes... Either of those solutions don't work (for me anyway) or they're wrong and not amply tested.

So here's how I was able to do it the neat and harmonious (read zen) way:

select
    datetimestamp, id, username
from 
    table1
order by 
    case @OrderBy 
        when 0 then datetimestamp ASC
        when 1 then username ASC
        when 2 then id
    end,
    case @OrderBy 
        when 0 then username
        when 1 then id
        when 2 then datetimestamp ASC
    end,
    case @OrderBy 
        when 1 then datetimestamp ASC
    end

Hope it is as enlightening for you as it was for me.