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
DateTimeStamp, ID, UserName
CASE @OrderBy
WHEN 0 THEN DateTimeStamp ASC, UserName ASC
WHEN 1 THEN UserName ASC, ID ASC, UserName ASC
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 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:

datetimestamp, id, username
order by
case @OrderBy
when 0 then datetimestamp ASC
when 1 then username ASC
when 2 then id
case @OrderBy
when 0 then username
when 1 then id
when 2 then datetimestamp ASC
case @OrderBy
when 1 then datetimestamp ASC
Hope it is as enlightening for you as it was for me.