Sorting and paging in SQL 2005 the easy and efficient way

The example below shows how to write an efficient, easy to maintain, and easy to understand way of using SQL 2005 features to implement sorting and paging of data. @StartRowIndex is 1 based, and is the row number (NOT the PAGE number). The @StartRowIndex can be calculated by int startRowIndex = Math.Ceiling(pageIndex * pageSize); though some type conversion will be needed in C#. The Order by CASE statements could be one long case statement with logs of when as long as the types of each column in the CASE statement is the same. I just created a new case statement for simplicity. However, you could create two in this case. One case statement that could have two when clauses (NAME, SEARCH_CODE), and another CASE statement with only one when clause since it is of a different data type that the other two columns. I have not found any performance issue with this. This includes using SQL Profiler to examine read, writes, duration. The number of case statements doesn't seem to have any recordable impact.

create proc GetPersonPage2005 @StartRowIndex int, @MaximumRows int, @Orderby varchar(50) as

SELECT [NAME], SEARCH_CODE, CREATED FROM (SELECT [NAME], SEARCH_CODE, CREATED, ROW_NUMBER() OVER ( ORDER BY -- add columns to sort by here CASE @Orderby WHEN 'NAME DESC' THEN [NAME] END DESC, CASE @Orderby WHEN 'NAME ASC' THEN [NAME] END ASC, CASE @Orderby WHEN 'SEARCH_CODE DESC' THEN SEARCH_CODE END DESC, CASE @Orderby WHEN 'SEARCH_CODE ASC' THEN SEARCH_CODE END ASC, CASE @Orderby WHEN 'CREATED DESC' THEN CREATED END DESC, CASE @Orderby WHEN 'CREATED ASC' THEN CREATED END ASC ) as RowNum FROM ALL_PERSON e ) as Tbl WHERE RowNum BETWEEN @StartRowIndex AND (@MaximumRows + @StartRowIndex - 1)

Order by RowNum ASC go

An example of using this stored procedure would be:

To sort by SEARCH_CODE in Ascending Order use:

GetPersonPage2005 3, 6, 'SEARCH_CODE ASC'

or to sort by SEARCH_CODE in Descending Order: GetPersonPage2005 3, 6, 'SEARCH_CODE DESC'