T-SQL Dynamic Order By

When you have some list of records in web or Windows application, sooner or later your client will say: Hey could you make columns sortable? It would be great if I could just click on header and order rows?

When coders try to implement dynamic Order By in T-SQL, first logical approach is to create variable and place column name in it. For example:

It seems logical on first sight, but this approach is not working. Variable is not understood as column, but as string value. Thus, rows will be ordered by constant. In other words, rows will not be ordered on any way.

So, what to do? Fortunately, there is better solution than writing several same queries with different ORDER BY clause. Check this example with using of CASE:

One more variation of same idea is to use ROW_NUM() function. If table contains lots of records, it’s better to provide paging. It’s usually bad idea to show thousands or millions of records in grid. For paging and ordering at same time, we can use ROW_NUMBER() function.

Here is example stored procedure which returns data, both paged and ordered, based on 4 input parameters:

In addition to these 4 parameters, based on your business needs you could add additional parameters which will be used in WHERE clause to filter the data.

Share on FacebookShare on LinkedInEmail this to someoneTweet about this on TwitterShare on Google+