Dynamic sorting with T-SQL

This is a migrated post from my former blog at bloodsweatand.net. It was originally posted 2008-10-27.

Almost all stored procedures that I write whose job it is to fetch data has to sort the data according to one or several columns. More often than not the selection of those columns, and the sort direction, is determined by the web application and therefore has to be based on parameters.

One simple and quite common solution to this is to build the whole query as a string and execute it with EXEC or sp_executesql. This way the stored procedure doesn't have to know anything about the sort order and the web application is free to specify exactly how it wants the data sorted. This comes at a price however as the database server won’t be able to cache the query plan for the query and the web application has to deal with building SQL,which goes against the separation of concerns between the application layer and the storage layer.

I've spent quite some time researching other ways to do dynamic sorting with T-SQL without using dynamic SQL. Sadly I've been unable to find a perfect solution that can do everything we can when using dynamic SQL. However, if we are willing to limit ourselves to a fixed number of sortable columns it's possible to achieve otherwise completely dynamic sorting by using several CASE statements.

Let's say we have a table with images in it. We will want to sort either by file name or by upload date. Our stored procedure may then look something like this:

CREATE PROCEDURE [GetImages]    

     @intSortField int = 0 --0 = Name, 1 = UploadDate   

    ,@intSortDirection int = 0 --0 = Ascending, 1 = Descending  

AS  

BEGIN   

    SET NOCOUNT ON;   

    SELECT ID, FileName, UploadDate   FROM Images   

    ORDER BY     

    CASE WHEN @SortField = 0 AND @SortDirection = 1 THEN FileName END DESC,    

    CASE WHEN @SortField = 0 THEN FileName END,   

    CASE WHEN @SortField = 1 AND @SortDirection = 1 THEN UploadDate END DESC,    

    CASE WHEN @SortField = 1 THEN UploadDate END,    

    ID  -- Use ID as a secondary sort field

END

The parameters @SortField and @sortDirection may map against two enums in the web application such as these:

public enum ImageSortField

{

     FileName = 0

    ,UploadDate = 1

}

public enum ImageSortDirection

{

     Ascending = 0

    ,Descending = 1

}

If we want to be able to specify two columns to sort by we can simply add two extra parameters to our stored procedure:

CREATE PROCEDURE [GetImages]    

     @intPrimarySortField int = 0 --0 = Name, 1 = UploadDate   

    ,@intPrimarySortDirection int = 0 --0 = Ascending, 1 = Descending  

    ,@intSecondarySortField int = 0

    ,@intSecondarySortDirection int = 0

AS  

BEGIN   

    SET NOCOUNT ON;   

    SELECT ID, FileName, UploadDate   FROM Images   

    ORDER BY     

    CASE WHEN @intPrimarySortField = 0 AND @intPrimarySortDirection = 1 THEN FileName END DESC,    

    CASE WHEN @intPrimarySortField = 0 THEN FileName END,   

    CASE WHEN @intPrimarySortField = 1 AND @intPrimarySortDirection = 1 THEN UploadDate END DESC,    

    CASE WHEN @intPrimarySortField = 1 THEN UploadDate END,   

    CASE WHEN @intSecondarySortField = 0 AND @intSecondarySortDirection = 1 THEN FileName END DESC,    

    CASE WHEN @intSecondarySortField = 0 THEN FileName END,   

    CASE WHEN @intSecondarySortField = 1 AND @intSecondarySortDirection = 1 THEN UploadDate END DESC,    

    CASE WHEN @intSecondarySortField = 1 THEN UploadDate END,    

    ID  -- Use ID as a secondary sort field

END

When you have more columns you wish to be able to sort by and/or want a tertiary sort column this method will result in a pretty long stored procedure. In my opinion it is however worth it to achieve separation of concerns between the application and the database. I’ll leave the performance comparison between the two methods discussed here for another article.

PS. For updates about new posts, sites I find useful and the occasional rant you can follow me on Twitter. You are also most welcome to subscribe to the RSS-feed.