Stored procedure for page results and sort by different columns

 

Here is a stored procedure for MS SQL Server that can be used to page the results and allow it to be sorted by different columns. It is fast and easy to use. Enjoy.

--GetPersonPage2005 5, 20, 'NAME DESC'
--GetPersonPage2005 null, 20, 'NAME DESC'
--GetPersonPage2005 5, 20, 'SEARCH_CODE ASC'

alter proc GetPersonPage2005
@StartRowIndex decimal(18,0),
@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