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.