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.

If you want to add or subtract some time from DateTime value, SQL Server has very useful DATEADD function.

For example, to add two days to SomeDate field, use this code:

SELECT DATEADD(dd, 2, SomeDate) FROM SomeTable;

To subtract time, just use negative value of second parameter.

Syntax of DATEADD function is:

DATEADD( TimeIntervalName, Value, FieldOrVariable )

In previous example, d is abbreviation for day. Here is complete list of interval names and their abbreviations that can be used with DATEADD function:

Nanosecond: ns
Microsecond: mcs
Millisecond:  ms
Second:  ss or s
Minute: mi or n
Hour: hh
WeekDay: dw or w
Week: wk or ww
Day: dd or d
DayOfYear: dy or y
Month:  mm or just m
Quarter:  qq or q
Year:  yy or yyyy

Note that Nanosecond and Microsecond can be used only on SQL Server 2008 or later.