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:

SELECT * FROM SomeTable ORDER BY @Variable

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:

CREATE PROCEDURE uspGetOrderedItems
(
  @sortField VARCHAR(100) = 'ItemName',
  @sortDirection VARCHAR(4) = 'DESC'
)
AS
BEGIN         
  SET NOCOUNT ON

  SELECT * FROM Item 

  ORDER BY 
    (CASE WHEN @sortField = 'ItemName' AND @sortDirection = 'ASC' THEN ItemName END) ASC,
    (CASE WHEN @sortField = 'ItemName' AND @sortDirection = 'DESC' THEN ItemName END) DESC,
    (CASE WHEN @sortField = 'Price' AND @sortDirection = 'ASC' THEN Price END) ASC,
    (CASE WHEN @sortField = 'Price' AND @sortDirection = 'DESC' THEN Price END) DESC;
END

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:

CREATE PROCEDURE uspGetOrderedItemsWithPaging
(
  @sortField VARCHAR(100) = 'ItemName',
  @sortDirection VARCHAR(4) = 'DESC',
  @startRow int = 1,
  @endRow int = 20
)
AS
BEGIN         
  SET NOCOUNT ON

  SELECT * FROM 
    (
    SELECT ROW_NUMBER() OVER (	
      ORDER BY 
        (CASE WHEN @sortField = 'ItemName' AND @sortDirection = 'ASC' THEN ItemName END) ASC,
        (CASE WHEN @sortField = 'ItemName' AND @sortDirection = 'DESC' THEN ItemName END) DESC,
        (CASE WHEN @sortField = 'Price' AND @sortDirection = 'ASC' THEN Price END) ASC,
        (CASE WHEN @sortField = 'Price' AND @sortDirection = 'DESC' THEN Price END) DESC
      ) AS ROW_NUM, *
    FROM Item
    ) Result
    WHERE ROW_NUM BETWEEN @startRow AND @endRow;

END

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.