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:
The parameters @SortField and @sortDirection may map against two enums in the web application such as these:
If we want to be able to specify two columns to sort by we can simply add two extra parameters to our stored procedure:
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.
- My favorite way to do paging with T-SQL
- Manage multiple web.config files using Phantom
- A neat little type inference trick with C#
- Learning Scala part five - Methods
- Learning Scala part three – Executing Scala code
- My development toolbox
- A few new tools in my .NET development toolbox
- Using MSpec – a few weeks in