Dynamic Sorting in SQL Stored Procedures

July 26, 2005

One of the biggest limitations of using Stored procedures vs. dynamic SQL is the severe restrictions of sorting.  In SQL Server it is not possible to programatically specify a sort order such as this:

Order By @SortCol1 @Direction, @SortCol2, …

There is however, a way of writing a Stored Procedure sort that does the equivalent, allowing you to pass the sort order as a parameter.  Admittedly it’s not ideal, but it is much faster than re-sorting the results using client-side code, XSL, or many of the other alternatives.  To accomplish this all you need to do is define one or more calculated fields in your query, and a parameter to control them.

For instance:

create procedure X
  @P1 int,
  @P2 char(10),
  @SortOption
  tinyint =0
as
Begin
Select A, B, C,
  case @n
    when 0
      then convert( char(10), A )
    when 1
      then B
    when 2
      then convert( char(10), C )
  else ‘ ‘
end as SortCOlumn1,
  case @n
    when 10
      then convert( char(10), A )
    when 11
      then B
    when 12
      then convert( char(10), C )
  else ‘ ‘
end as SortCOlumn2,
  where B=@P2 or A=@P1 or …
  order by SortColumn1 asc, SortColumn2 desc
End

By passing in a value for @SortOption you can indirectly control which field is used for sorting. Note that all the choices should be converted to the same datatype, I found that SQL Server give syntax errors if you don’t. Also be sure to use the else ‘ ‘, this allows the first sort to be ignored when you want to sort in descending order, and vice versa.

So there you have it, once compiled this solution is quite efficient. I don’t have any numbers handy to prove it (yet), but initial impressions were that it would easily beat sorting the end results in an XSL transformation or in a .Net dataview object.